Romelsb - With B1 selected define the name TextCalc and enter the
formula as below:

B1: =IF(H15<"",TextCalc&" LITERS")

TextCalc: =EVALUATE(SUBSTITUTE(!A1,"LITERS",""))


romelsb wrote:

Lori its a nice catch...I am also interested to know a formula when my text
is like this in a cell A1 "20 LITERS + 50 LITERS" THEN THE formula RESULT TO
BE in cell B1 "70 LITERS".

"Lori" wrote:

A1: 220g or 400 ml
B1: =LOOKUP(9^99,--LEFT(A1,{1,2,3,4,5,6,7,8,9,10}))
C1: =TRIM(SUBSTITUTE(A1,B1,""))


John S wrote:

I'd like to split cells with a combined number followed by text as you find
in size and UOM information. For example it would split 220g into one cell of
220 and the other as g. For a cell with 400 ml then it would be 400 and ml in
the 2 cells.