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
  • 109
  • What application are you using? – fixer1234 Oct 06 '18 at 04:05
  • 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 Oct 09 '18 at 22:26
  • 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 Oct 09 '18 at 22:26

0 Answers0