Jun 14 2023 07:54 AM
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 |
Jun 14 2023 08:07 AM
Jun 14 2023 08:12 AM
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
Jun 14 2023 08:18 AM - edited Jun 14 2023 08:51 AM
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.
Jun 14 2023 08:26 AM
Jun 14 2023 08:27 AM
Jun 14 2023 08:34 AM
SolutionI 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.
Jun 14 2023 08:34 AM
SolutionI 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.