CODEWITHSUNDEEP
stringexcel
Bramat
Bramat

Reputation: 997

Substring in Excel2010

I'm working on an Excel file and I need to seperate a long string divided by "|" into different strings for example: "AB100|AB101|AB102|AB103"

I need to seperate them into - AB100, AB101, AB102, AB103

Thank you!

Upvotes: 1

Views: 86

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

With your data in A1, in B1 enter:

=TRIM(MID(SUBSTITUTE($A1,"|",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across.

Upvotes: 0

xlm
xlm

Reputation: 7614

From the Ribbon, under the 'Data' tab, select 'Text to Columns' and select the appropriate delimiter, in this case it is |

Illustrative screenshot on where to find it and the output: enter image description here

Note I have done it with two copies of your example string to show a before and after. It will replace the original string in A1 with the values split across the columns.

Upvotes: 1

Related Questions