splitting cells

[wayneg]

i want to split a cell with a formula and not use text to columns. i will have a cell a1 for example with 1d 4h 36m 34s in and want to have 4 formulas 1 in
a5
a6
a7
a8

.. so the result will be

1
4
36
34

i know you can split with left, mid and right but dont know how to do it with the desired results

thanks,

wayne

[cbatrody]

Hi,

Can you please post few more examples?

[GeneralDisarray]

If the text/numbers are exactly as you describe (no extra spaces on front or back) use:

A5
=LEFT(A1,FIND("d",A1)-1)

A6
=MID(A1,FIND("d ",A1)+2,FIND("h ",A1)-FIND("d ",A1)-2)

A7
=MID(A1,FIND("h ",A1)+2,FIND("m ",A1)-FIND("h ",A1)-2)

A8
=MID(A1,FIND("m ",A1)+2,FIND("s",A1)-FIND("m ",A1)-2)

[Glenn Kennedy]

Beaten to it... Same approach taken by me, except that I used search (case insensitive) instead of find.

[wayneg]

obviously the numbers will change and they can go into single figures or double.. will that make a difference

[wayneg]

thank you both

[Glenn Kennedy]

Single or double will be fine.

[AlKey]

Or with one formula

=LOOKUP(25^25,1*LEFT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",255)),(ROWS($A$1:A1)-1)*255+1,255)),ROW(A$1:A$10000)))

	A	B
1	1d 4h 36m 34s	1
2		4
3		36
4		34