CODEWITHSUNDEEP
excelsplit
Mr Mystery Guest
Mr Mystery Guest

Reputation: 1474

Split cell on delimiter using formulas

Is it possible (using formulas) to split a string (in cell A1) into it's component parts via a delimiter? Where the delimiter is always the same, the Initial string is always in 4 parts. But the lengths of those substrings vary.

Current Job - VARK06_16 - 099 - ABC1

Becomes

Current Job
VARK06_16
099
ABC1

in cells B1,C1,D1 & E1 respectively.

Upvotes: 0

Views: 2645

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Put this formula in B1 and copy down:

=TRIM(MID(SUBSTITUTE($A$1,"-",REPT(" ",999)),(ROW(1:1)-1)*999+1,999))

enter image description here

if you want in the same row then change the ROW(1:1) to Column(A:A):

=TRIM(MID(SUBSTITUTE($A$1,"-",REPT(" ",999)),(Column(A:A)-1)*999+1,999))

And drag accross.

Upvotes: 3

Jeremy Young
Jeremy Young

Reputation: 304

this is quite easy to do so long as you are willing to have one column of extra formulae, to locate the positions of your delimiters. here is a screenshot

to work out the delimiter locs use find and the location of the previous value - so the equation in B4 is =FIND("-",A$2,B3+1)

to extract the sub-strings use MID and the values from the delimiter locs columns. So the equation in C4 is =MID(A$2,B3+2,B4-B3-2)

There will be other ways to do this but this works.

Upvotes: 2

Related Questions