CODEWITHSUNDEEP
excelexcel-formulaspreadsheetvba
Carol.Kar
Carol.Kar

Reputation: 5355

Excel - Split by `;` in each cell

I would like to split these texts into cells in excel:

Hello-HOW-are-YOU-DOING-!
This-is-not-my-name-.
Random-People-are-looking-?
No-!

It should basically look like that:

enter image description here

At the moment I am using this formula, which gives me just the first split:

=LEFT(Export!A2;FIND("-";Export!A2)-1)

Any recommendations how I can do the split automatically for each -?

I appreciate your replies!

Upvotes: 0

Views: 217

Answers (3)

JvdV
JvdV

Reputation: 75870

In Excel O365 with spilling this can now be done through:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B2,"-","</s><s>")&"</s></t>","//s"))

And from Excel 2013 onwards you could have still used FILTERXML but with INDEX and drag to the right:

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(B2,"-","</s><s>")&"</s></t>","//s"),COLUMN(A1))

Upvotes: 0

pugmastaflex
pugmastaflex

Reputation: 470

Or you could do this...

=MID($A2,FIND(B2,$A2)+LEN(B2)+1,FIND("-",$A2,FIND(B2,$A2)+LEN(B2)+1)-(FIND(B2,$A2)+LEN(B2)+1))

in cell D onwards since you already have 

=LEFT(A2,FIND("-",A2)-1)

for the first cell

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

With data in B1; in C1 enter:

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

and copy across:

enter image description here

Upvotes: 4

Related Questions