Thread: Cell Calculation with Text

How can I get excel to calculate with a cell if its contents is a number and letters?

Eg.

A1= 2
A2= 230gr

I want to calculate cell A2 multiplied by A1 (A1*A2) the answer is supposed to be "460"

If we assume the right two characters of A2 are always text then:

=A1*LEFT(A2,LEN(A2)-2)

Will the string always be NNNxx, where N= numeric and x = alpha?

depends what's actually in a2 but
=A1*LOOKUP(9.99E+307,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)))))
is one way

Well my strings might all be different.

I might have string vary from 2-to-4 Numeric values and 1-to-2 alpha

It would be best to get rid of the text, and put the units, if necessary, in another column.

You could use a User Defined Function to remove the text characters...

Public Function RmvText(inString As String)
    Dim x           As Integer    'LOOPING COUNTER
    Dim vText       As Variant    'CHARACTER HOLDER

    For x = 1 To Len(inString)
        vText = Mid(inString, x, 1)
        If IsNumeric(vText) Or vText = "." Then RmvText = RmvText & vText
    Next x

End Function

Originally Posted by dangelor

You could use a User Defined Function to remove the text characters...

Public Function RmvText(inString As String)
    Dim x As Integer
    Dim vText As Variant
    For x = 1 To Len(inString)
        vText = Mid(inString, x, 1)
        If IsNumeric(vText) Then RmvText = RmvText & vText
    Next x
End Function

I will never figure out how and where to use this, but thanks.
.
.

Originally Posted by shg

It would be best to get rid of the text, and put the units, if necessary, in another column.

Been thinking the same thing. but then its alot of work , and the sheet might get confusing to idiots who dont understand it (when I'm ready to share it)

@martindwilson, your formula is returning "TRUE" instead of a number. I am trying to understand the concept but I can't fully understand the principles used here.

abousetta

Martin's formula returns a valid number (460, using your examples in post #1) for me.
Perhaps you should upload a sample workbook.

Palmetto, you are right. I opened a new workbook and tested it again and it worked this time.

abousetta

I will never figure out how and where to use this, but thanks

See attached file.

Would it not be easier to format A2 & A3
Format Cells > Number > Custom
Type:=

#,##0"gr"

Then in A3

=A1*A2

Just a thought.

What I am basically trying to do it calculate the cost of food items per gram or per milliliter and then see which on is the cheapest by the Kilo/Kilogram or Liter

I've altered a function to something like this:
=(-A1/LOOKUP(0,-LEFT(A2,{1,2,3,4})))*1000
Where A1 is the cost of the item and A2 is the quantity of the item (in grams, milliliters, liters and kilo/kilogram,)
Which is in layman's =(cost of food divided by how much ml/g received) * 1000

Spaces
If I have "121gr" in a cell it works perfectly.. but when I have "121 gr" in a cell it doesnt *notice the spacing between the alpha and numeric value*

Different unit conversion? (convert KG and L into milliliters and grams)
Sometimes I enter my data with: "kg" "L" and "K", examples:

• 1kg
• 1.2 L
• 2.5k
• 2.5 KG
• 1.4L

*notice the spacing between the alpha and numeric value*

You would be much better doing as shg suggested, put the units in a seperate cell.