Help With Splitting Text String With Formulas

[denzo36]

Hi guys,

I have a text string coming in from a SQL server query to Excel that is made up of 4 parts always separated by a ':' ';' and ',' each time.
I need to try and extract each part of the text string into the 4 bits and can't seem to get the formulas correct. Unfortunately I can't go the VBA or Power Query route, it has to be excel formulas.

The left and right bits are easy to get via the LEFT and RIGHT functions - how would I get the middle two bits?

Examples of the text string are:

John Murphy:Full License;01,33
Mary Burke:Provisional License;0,45
David Bing:Full License;04,87
Sarah James:Provisional License;05,50

As you notice,first part of the string is the start of the string to the ':' second part of the string is what's in between the ':' and the ';' third part of the string is what's in between the ';' and the ',' and final part of the string is from the ',' to the end of the string

Any ideas?

 

[mikerickson]

This will get the string between : and ;

=MID(A11, FIND(":",A11)+1, FIND(";",A11)-FIND(":",A11)-1)

similarly for ; and ,

 

[alansidman]

Here is an alternative solution using Get & Transform which is on the Data Tab of the Ribbon. Mcode follows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({":",";",","}, QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"})
in
    #"Split Column by Delimiter"

Book12
	A	B	C	D
1	Column1.1	Column1.2	Column1.3	Column1.4
2	John Murphy	Full License	01	33
3	Mary Burke	Provisional License	0	45
4	David Bing	Full License	04	87
5	Sarah James	Provisional License	05	50
Sheet2

 

[Peter_SSs]

Any ideas?

You could also use Text to Columns -> Delimited with ...

.. then either 'Finish' or, if you want the leading zeros in the third column, click 'Next', select that third column in the bottom section and choose 'Text' in th top part, then 'Finish'

 

[Peter_SSs]

.. or this formula copied across and down to extract all parts.

20 06 18.xlsm
	A	B	C	D	E
1	John Murphy:Full License;01,33	John Murphy	Full License	01	33
2	Mary Burke:Provisional License;0,45	Mary Burke	Provisional License	0	45
3	David Bing:Full License;04,87	David Bing	Full License	04	87
4	Sarah James:Provisional License;05,50	Sarah James	Provisional License	05	50
Split Text

Cell Formulas
Range	Formula
B1:E4	B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,":",","),";",","),",",REPT(" ",100)),COLUMNS($B:B)*100-99,100))

 

[mikerickson]

Or you could use Text To Columns, Delimited with the boxes for semicolon and comma checked with the colon as the Other character.

 

[Peter_SSs]

Or you could use Text To Columns, Delimited with the boxes for semicolon and comma checked with the colon as the Other character.

Perhaps you missed post #4?

 

[denzo36]

Hi all, really appreciate everyone taking the time to look at this.

.. or this formula copied across and down to extract all parts.

20 06 18.xlsm
	A	B	C	D	E
1	John Murphy:Full License;01,33	John Murphy	Full License	01	33
2	Mary Burke:Provisional License;0,45	Mary Burke	Provisional License	0	45
3	David Bing:Full License;04,87	David Bing	Full License	04	87
4	Sarah James:Provisional License;05,50	Sarah James	Provisional License	05	50
Split Text

Cell Formulas
Range	Formula
B1:E4	B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,":",","),";",","),",",REPT(" ",100)),COLUMNS($B:B)*100-99,100))

Peter this one is working perfectly for me and is just what I need.

Many thanks.

 

[Peter_SSs]

Peter this one is working perfectly for me and is just what I need.

Many thanks.

You're welcome. Thanks for the follow-up.

 

[mikerickson]

Perhaps you missed post #4?

Yes I did.