Ho do I remove numbers from a cell that has alphabetic charecters?

[Karnik]

Hello,
I am trying to extract numbers from given data ( from a column) sample given below.
Without using VB any suggestions?
I wish to keep alphabets those appear after numbers...
Thanks
Karnik

ALLEN'S MINTIES 1KG
ALMOND KERNELS SLICED 1K
APPLE JUICE L/L 2L
ARNOTT WATER ******* 125G
BALSAMIC VINEGAR 500M
BASMATI RICE 5KG
BROWN SUGAR 1K
CASTER SUGAR 1K
CHEF CHOICE COCONUT CREAM 400ML
CHILLIES GROUND MILD 500G

 

[tusharm]

What would be the desired results for each of the examples you've shown?

If it is 1K, 1K, 2L, 125G, etc., then are those numbers and measuring units always the last token in the text?

Hello,
I am trying to extract numbers from given data ( from a column) sample given below.
Without using VB any suggestions?
I wish to keep alphabets those appear after numbers...
Thanks
Karnik

ALLEN'S MINTIES 1KG
ALMOND KERNELS SLICED 1K
APPLE JUICE L/L 2L
ARNOTT WATER ******* 125G
BALSAMIC VINEGAR 500M
BASMATI RICE 5KG
BROWN SUGAR 1K
CASTER SUGAR 1K
CHEF CHOICE COCONUT CREAM 400ML
CHILLIES GROUND MILD 500G

 

[Robert Mika]

Doeas this help?

Excel 2010
	A	B
1	ALLEN'S MINTIES 1KG	1KG
2	ALMOND KERNELS SLICED 1K	1K
3	APPLE JUICE L/L 2L	2L
4	ARNOTT WATER ******* 125G	125G
5	BALSAMIC VINEGAR 500M	500M
6	BASMATI RICE 5KG	5KG
7	BROWN SUGAR 1K	1K
8	CASTER SUGAR 1K	1K
9	CHEF CHOICE COCONUT CREAM 400ML	400ML
10	CHILLIES GROUND MILD 500G	500G
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",99)),99))

Drag formula down

 

[Karnik]

Robert,
Thanks once again, it works !
I appreciate your response,
Cheers
Karnik

 

[Karnik]

Hi Robert,
Now I realised I need to use these numberic data for calculations ! I am better of using labourious "filter" method?
Any suggestions?
Thanks
Karnik

 

[Robert Mika]

Consider this UDF

Option Explicit
Function MSUBSTITUTE(ByVal trStr As Variant, frStr As String, toStr As String) As Variant
' Created by Juan Pablo González
' with ideas from Aladin Akyurek
'toStr is assumed to be the same length of frStr.  If not, the remaining characters
'will be considered as null ("").
'Note that this function IS case sensitive.  To replace all instances of "a" you need
'to use "a" AND "A"
'You can't replace one character with two characters. This
'=MSUBSTITUTE("This is a test","i","$@")
'will result in this:
'"Th$s $s a test"
    Dim iRow As Integer
    Dim iCol As Integer
    Dim j As Integer
    Dim Ar As Variant
    Dim vfr() As String
    Dim vto() As String
    
    ReDim vfr(1 To Len(frStr))
    ReDim vto(1 To Len(frStr))
    
    For j = 1 To Len(frStr)
        vfr(j) = Mid(frStr, j, 1)
        If Mid(toStr, j, 1) <> "" Then
            vto(j) = Mid(toStr, j, 1)
        Else
            vto(j) = ""
        End If
    Next j
    
    If IsArray(trStr) Then
        Ar = trStr
        For iRow = LBound(Ar, 1) To UBound(Ar, 1)
            For iCol = LBound(Ar, 2) To UBound(Ar, 2)
                For j = 1 To Len(frStr)
                    Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), vfr(j), vto(j))
                Next j
            Next iCol
        Next iRow
    Else
        Ar = trStr
        For j = 1 To Len(frStr)
            Ar = Application.Substitute(Ar, vfr(j), vto(j))
        Next j
    End If
    MSUBSTITUTE = Ar
End Function

Thern in C2:
=MSUBSTITUTE(B2,"KGLM","")+0

to get

A</SPAN>	B</SPAN>	C</SPAN>
ALLEN'S MINTIES 1KG</SPAN>	1KG</SPAN>	1</SPAN>
ALMOND KERNELS SLICED 1K</SPAN>	1K</SPAN>	1</SPAN>
APPLE JUICE L/L 2L</SPAN>	2L</SPAN>	2</SPAN>
ARNOTT WATER ******* 125G</SPAN>	125G</SPAN>	125</SPAN>
BALSAMIC VINEGAR 500M</SPAN>	500M</SPAN>	500</SPAN>
BASMATI RICE 5KG</SPAN>	5KG</SPAN>	5</SPAN>
BROWN SUGAR 1K</SPAN>	1K</SPAN>	1</SPAN>
CASTER SUGAR 1K</SPAN>	1K</SPAN>	1</SPAN>
CHEF CHOICE COCONUT CREAM 400ML</SPAN>	400ML</SPAN>	400</SPAN>
CHILLIES GROUND MILD 500G</SPAN>	500G</SPAN>	500 </SPAN>

<TBODY>
</TBODY>

You can add as another letters to the formula second argument

 

[mikerickson]

Would this worksheet function do?
=MID(A1, MIN(FIND({1,2,3,4,5,6,7,8,9}, A1&"123456789")), 255)