Forum Discussion
Solved
Split data in a single cell
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 ...
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.
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.
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
"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