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.

If there is always a space betwwen the number and the unit of measure then

=LEFT(A1,SEARCH(" ",A1)-1)
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

Regards,
Stefi

€žJohn S€� ezt Ã*rta:

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.

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.

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.

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.

Lori--i tried it the results remains with 20 + 50 liters , i miss the 70
liters result...pls. retry your help....thanks.

--
"Bright minds are blessed to those who share them.."-rsb.


"Lori" wrote:

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.

I think you need to enter LITERS in CAPS. Alternatively use UPPER(!A1)
instead of !A1.

romelsb wrote:

Lori--i tried it the results remains with 20 + 50 liters , i miss the 70
liters result...pls. retry your help....thanks.

--
"Bright minds are blessed to those who share them.."-rsb.

In B1:

=LEFT( A1,SUMPRODUCT( --ISNUMBER( 0 * LEFT( A1, ROW( INDIRECT( "1:" & LEN(
A1 ) ) ) ) ) ) )

In C1

=SUBSTITUTE(A1, B1, "" )

--
Regards,
Luc.

"Festina Lente"


"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.