Search code examples
excelvbaexcel-formula

Split text string in Excel by semicolons and space

Supposedly I have an excel column which looks like this:
enter image description here

How do I achieve this using excel formula:
enter image description here

Right now, I am able to retrieve these using:
enter image description here

=LEFT(D2,SEARCH(";",D2,1)-1) (First)

=MID(D2, SEARCH(";",D2) + 1, SEARCH(";",D2,SEARCH(";",D2)+1)-SEARCH(";",D2)-1) (Second)

=RIGHT(D2,SEARCH(";",D2,1)-1) (Third)

I'd realised that the text was cut off for the RIGHT formula. How can I separate the text using CHAR(10) when each semicolon is found?

Solution

  • You need to replace the semi-colon and space with a semi-colon and new line character.

    Use =SUBSTITUTE(A1,"; ", ";" & CHAR(10)) and ensure the cell has text wrapping on.