How can i split combined number and text in excel using formula?

[John S]

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.

 

[Stefi]

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:

 

[Lori]

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,""))

 

[PapaDos]

In B1:

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

In C1

=SUBSTITUTE(A1, B1, "" )

 

[romelsb]

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]

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]

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

 

[Lori]

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

 

[Lori]

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

 

[romelsb]

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

 

[Lori]

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.

 

[romelsb]

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/com...rosoft.public.excel.worksheet.functions&fltr=

I hope you can join here cause this will enlighten all of us about tips on
Stable formulation....Regards

 

[Lori]

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

 

[romelsb]

THATS GREAT...THANKS ALOT...WISH TO SEE U IN FUTURE THREADS....I HOPE JOHN S.
I CAN PLACE A CHECK MARK TO LORI.

 

[Lori]

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