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.

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.

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.

Apologies I just noticed formula referred to H15 for your example it
should be A1.
Now it should work

Lori wrote:

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.

LORI...THANKS yet....
A1 = TEXT : 20 LITERS + 50 LITERS
DESIRED RESULT ON B1 : 70 LITERS

TextCalc: =SUBSTITUTE(A1,"LITERS","") : RESULT is 20 + 50
B1: =IF(A1<"",TextCalc&" LITERS") : RESULT is 20 + 50 LITERS

I remove "EVALUATE" because of message "that function is not valid" - excel
2003 - does this comes from an add-ins....


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


"Lori" wrote:

Apologies I just noticed formula referred to H15 for your example it
should be A1.
Now it should work

Lori wrote:

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.

You need to enter =EVALUATE() as a name as it is a macro function, it
will not work as a worksheet function. To see how it works try this
example:

Choose Insert Name Define type Test as the name and in the refers
to box type =EVALUATE("1+2"). If you enter =Test in a cell you should
get the answer 3.

My original posting should work - try it this way.

thanks for the experience reply...It does the trick for me...but I hope u
dont mind if I ask again for the last time how shall I write for 2,000 LITERS
+ 50.5 LITERS....
THE EVALUATE(ref) DOES WORK...YET now I have worries to use this based on
todays thread
http://www.microsoft.com/office/comm...ctions&f ltr=

I hope you can join here cause this will enlighten all of us about tips on
Stable formulation....Regards
--
"Bright minds are blessed to those who share them.."-rsb.


"Lori" wrote:

You need to enter =EVALUATE() as a name as it is a macro function, it
will not work as a worksheet function. To see how it works try this
example:

Choose Insert Name Define type Test as the name and in the refers
to box type =EVALUATE("1+2"). If you enter =Test in a cell you should
get the answer 3.

My original posting should work - try it this way.

For a general formula for your "Text Sum" problem, try this array
formula (ctrl+shift+enter to execute):

=SUM(IF(MMULT(--ISERR(--MID(SUBSTITUTE(A1,"+","|")&"|",(ROW(1:500)-1)/10+{0,1,1},MOD(ROW(1:500)-1,10)+{2,1,2})),{1;2;4})=5,--MID(A1,(ROW(1:500)-1)/10+1,MOD(ROW(1:500)-1,10)+1)))

It locates the numeric parts of a text statement and adds them together
(for up to 50 characters of text and numbers up to 10 digits). For both
the examples you gave it returns the correct value (you can add at the
end &" LITERS" to include the units).

HTH Lori

THATS GREAT...THANKS ALOT...WISH TO SEE U IN FUTURE THREADS....I HOPE JOHN S.
I CAN PLACE A CHECK MARK TO LORI.
--
"Bright minds are blessed to those who share them.."-rsb.


"Lori" wrote:

For a general formula for your "Text Sum" problem, try this array
formula (ctrl+shift+enter to execute):

=SUM(IF(MMULT(--ISERR(--MID(SUBSTITUTE(A1,"+","|")&"|",(ROW(1:500)-1)/10+{0,1,1},MOD(ROW(1:500)-1,10)+{2,1,2})),{1;2;4})=5,--MID(A1,(ROW(1:500)-1)/10+1,MOD(ROW(1:500)-1,10)+1)))

It locates the numeric parts of a text statement and adds them together
(for up to 50 characters of text and numbers up to 10 digits). For both
the examples you gave it returns the correct value (you can add at the
end &" LITERS" to include the units).

HTH Lori

Thanks, hope to contribute more in the future...