0
I have a column in SharePoint containing information like this:
ABCD-D-12345678-10012-.PDF
XYZAB-D1-87654321-2110-.PDF
ABCD-D0-12345678-10012-.PDF
XYZAB-D1-87654321-2110-.PDF
I need to break down this file name into the following groups:
Product# (first part of the string, includes the first dash)
ABCD-D
XYZAB-E1
ABCD-A0
XYZAB-D1
Serial# (second part of the string, ignores dashes)
12345678
87654321
12345678
87654321
Part# (third part of the string, ignores dashes)
10012
2110
10012
2110
I'm having a hard time getting "LEFT"; "MID" and "RIGHT" to ignore the first dash to create the first group. The same is true finding the items that are in the mid section.
I've tried this =LEFT(Name1,INT(FIND("-",Name1))) but I need it to include the first dash and the rest of the characters until the next dash.
The goal is to be able to create three calculated columns where each one of them extracts the data as described above.
Pucho
Posted 2018-10-05T18:35:53.887
Reputation: 9
What application are you using? – fixer1234 – 2018-10-06T04:05:27.190
You've returned to the post several times, but haven't provided any clarification. Sharepoint isn't an application, it's just a file management system that can be used with many applications. Your commands look like they could be Excel (if so, what version?), but wouldn't be limited to Excel. Your PDF names could be spreadsheet cell content, which could fit with the task, although you refer to it as Sharepoint information. Is Name1 a named range? A field name? (cont'd) – fixer1234 – 2018-10-09T22:26:20.503
If this is Excel, a simple, low-tech solution would be to start with a series of helper cells to calculate the location of each hyphen. You could do this by using FIND with MID, with starting points at where the last hyphen was found (and arithmetic for the location in the original string). Use those results with MID to extract the desired strings. Once you have it worked out, you can substitute the helper cell formulas for the helper cell references to reduce it to standalone formulas that don't require helper cells. – fixer1234 – 2018-10-09T22:26:28.633