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?
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?