Formula needed!

[HelpWithExcel1]

Hi,
I need a formulae (if possible) for the following but I'm not great with excel so any help is appreciated. I have a list of cells in column A which have data such as ''TP101 0.00-0.10 1' which is together in one cell. (which for anyone that is wondering is a location, a top depth, a base depth and ref number)

What I need to do is split the cell in an easier than manual way so that column B takes the 'TP101' column C takes the '0.00' and column D picks out the 0.10' and the final 1 is no use for what I need.

Is there an easy way to do this?

Any help is greatly appreciated as I have thousands of rows which follow the same format and need splitting out

Thanks

 

[Jonmo1]

Welcome to the board..

Are the strings in a consistent pattern?
the first part is always 5 characters
then a space
then #.##
then -
then #.##

?

 

[Jonmo1]

If that IS a consistent pattern with your data, then you can use some fairly simple Left and Mid functions

=LEFT(A1,5) ' gets TP101
=MID(A1,7,4) ' gets 0.00
=MID(A1,12,4) ' gets 0.10


If that is NOT a consistent pattern, then perhaps this one formula entered and dragged to the right
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"-"," ")," ",REPT(" ",LEN($A2))),(COLUMNS($A$1:A$1)-1)*LEN($A2)+1,LEN($A2)))

Assuming at least that:
there is always a space between the 1st 2 parts
there is always a hyphen between the 2nd and 3rd parts
there is always a space after the 3rd part

 

[HelpWithExcel1]

If that IS a consistent pattern with your data, then you can use some fairly simple Left and Mid functions

=LEFT(A1,5) ' gets TP101
=MID(A1,7,4) ' gets 0.00
=MID(A1,12,4) ' gets 0.10


If that is NOT a consistent pattern, then perhaps this one formula entered and dragged to the right
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"-"," ")," ",REPT(" ",LEN($A2))),(COLUMNS($A$1:A$1)-1)*LEN($A2)+1,LEN($A2)))

Assuming at least that:
there is always a space between the 1st 2 parts
there is always a hyphen between the 2nd and 3rd parts
there is always a space after the 3rd part

Thanks for your responses! Yes the format is consistant, so that worked great!!

 

[Jonmo1]

You're welcome.