Split data in a single cell

[WSokol]

How can I split data in a single cell when there isn't a clear data separation point? I need to separate the two lines into columns.  The source data format cannot be changed.  I cannot figure out a text to columns delimitator since number of characters in the name and the ID number vary.

FName LName: Custom Identifier/External Payroll ID/11111

FName LName: Custom Identifier/Personal Identification Number/9999999

[#2]

If there are spaces, those can be delimiters; if there's a colon, that can be a delimiter. Can you post a copy (after eliminating identifiable names) on OneDrive or GoogleDrive with a link here that grants access?

[#3]

@WSokol 

With such a value in A2:

In B2:  =LEFT(A2,FIND(" ",A2)-1)

In C2:  =MID(A2,FIND(" ",A2)+1,FIND(":",A2)-FIND(" ",A2)-1)

In D2:  =MID(A2,FIND(":",A2)+2,FIND("/",A2)-FIND(":",A2)-2)

In E2:  =MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)

In F2:  =MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,100)

Fill down

[#4]

@WSokol 

Going off the provided string, you could use:

=TEXTSPLIT(A2,{":",": ","/"})

If there are more delimiters, a larger sample of strings might help us arrive at the best solution possible.

[#5]

Thank you, very close but this doesn't separate the end of the Payroll ID number from the 2nd FName
"11111FName LName: Custom Identifier"
This is the field when un-wrapped:
FName LName: Custom Identifier/External Payroll ID/11111FName LName: Custom Identifier/Personal Identification Number/9999999

[#6]

Thank you, very close but this doesn't separate the end of the Payroll ID number from the 2nd FName
"11111FName LName: Custom Identifier"
This is the field when un-wrapped:
FName LName: Custom Identifier/External Payroll ID/11111FName LName: Custom Identifier/Personal Identification Number/9999999

[#7 · Accepted solution]

@WSokol 

I thought there were 2 cells.

Try Text to Columns with Delimited as type.

Tick the Other check box and press Ctrl+J in the box next to it. This is the code for a line break.

[#8]

That works, thank you so much!