Formula

[handysmurf]

OK ... I dont want to add two more places to enter information so I need to pull information from a particular cell. So the original information looks like this "11.559, 2.379" ... I planned to do this at some point so I was very careful to maintain that format. I just need to pull the two different numbers into two different cells.

Thanks in advance.

 

[handysmurf]

Also... =IF(C5113="l",VALUE(MID(TEXT(U5113,"hh:mm:ss"),4,2))+VALUE(RIGHT(TEXT(U5113,"hh:mm:ss"),2))/60,"") if the original number is over an hour this results in only 15.00 as the decimal. Is there a way to fix that for over an hour?

 

[hiker95]

handysmurf,

If cell A1 contains 11.559, 2.379

In B1 put the following formula: =TRIM(MID(SUBSTITUTE(", "&$A1,", ",REPT(" ",100)),100*COLUMNS(A1:$A1),100))+0

In C1 put the following formula: =TRIM(MID(SUBSTITUTE(", "&$A1,", ",REPT(" ",100)),100*COLUMNS($A1:B1),100))+0

 

[handysmurf]

Perfect Thank You!!

 

[hiker95]

Perfect Thank You!!

handysmurf,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Peter_SSs]

=TRIM(MID(SUBSTITUTE(", "&$A1,", ",REPT(" ",100)),100*COLUMNS($A1:B1),100))+0

handysmurf
FWIW, the TRIM functions in the suggested formulas are not required as adding zero effectively forces a TRIM by converting to a number anyway.

In case you are interested, here are a couple of simpler formulas that I think should do the same job for you.
=LEFT(A1,FIND(",",A1)-1)+0
=REPLACE(A1,1,FIND(",",A1),"")+0

 

[handysmurf]

just a note for anyone that finds this in a search at some later time.... I had to change this to format the result as a number otherwise it wouldn't sum in a later cell. resulting formula: "NUMBERVALUE(TRIM(MID(SUBSTITUTE(", "&$A1,", ",REPT(" ",100)),100*COLUMNS(A1:$A1),100))+0)"

Oddly the first one had to be formatted as numbervalue, the second did not.... Shrug

 

[hiker95]

just a note for anyone that finds this in a search at some later time.... I had to change this to format the result as a number otherwise it wouldn't sum in a later cell. resulting formula: "NUMBERVALUE(TRIM(MID(SUBSTITUTE(", "&$A1,", ",REPT(" ",100)),100*COLUMNS(A1:$A1),100))+0)"

Oddly the first one had to be formatted as numbervalue, the second did not.... Shrug

handysmurf,

Nicely done. Glad that you were able to figure it out.

Have a great day, and, come back to MrExcel anytime.