Separate numbers from Text

amna77 · May 17th, 2002, 11:54 AM

Hi,
Is there any way that you can Separate numbers from text.
I have numbers and text in one columnB.
Is there any macro that when I run, it should place numbers in column C. I have a data range from A:15 through A:500.

Thanks in advnace.

Nimrod · May 17th, 2002, 12:18 PM

I'm a little confused in that you say the data is in RowB then you talk about the range being A15:A500... Anyway this code "moves" numbers from Row B to C.
If you want it to be a "copy" instead of "move" then just take out the ".clear" from the following macro.
.. cheers

Code:

Public Sub MoveNumbers()
    For Each c In Range("B15:B500")
        With c
            If IsNumeric(.Value) Then
                    Range("C" & .Row).Value = .Value
                    .Clear
            End If
        End With
    Next c
End Sub

Nimrod · May 17th, 2002, 12:41 PM

This version clears value but leaves formatting etc intact .

Code:

Public Sub MoveNumbers()
    For Each c In Range("B15:B500")
        With c
            If IsNumeric(.Value) Then
                    Range("C" & .Row).Value = .Value
                    .value = ""
            End If
        End With
    Next c
End Sub

Please let me know if this works out for you.

_________________
NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.

Adieu,Nimrod

[ This Message was edited by: Nimrod on 2002-05-17 11:42 ]

amna77 · May 17th, 2002, 12:43 PM

In my column I have data like
13A
14L
8L
I want to get separate only numbers from letters. when I run a macro then it should give me value
13
14
8

Is there any macro like that???

Aladin Akyurek · May 17th, 2002, 12:48 PM

Quote:

On 2002-05-17 11:43, amna77 wrote:
In my column I have data like
13A
14L
8L
I want to get separate only numbers from letters. when I run a macro then it should give me value
13
14
8

Is there any macro like that???

Not interested in a formula that can do that?

amna77 · May 17th, 2002, 12:49 PM

we have a seious of values as below
1
2
3
4L
5M
6R
10M
12
41
I want to sort them in decening order.

amna77 · May 17th, 2002, 01:02 PM

Yea formula will do it as well.
Any formula how to separate numbers from text????????

Aladin Akyurek · May 17th, 2002, 01:07 PM

Quote:

On 2002-05-17 12:02, amna77 wrote:
Yea formula will do it as well.
Any formula how to separate numbers from text????????

In B1 enter and copy down:

=SUBSTITUTE(A1,SUBSTITUTE(A1,LEFT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))),""),"")+0

where A1 houses the first entry of relevant data.

Now you can sort on column B.

[ This Message was edited by: Aladin Akyurek on 2002-05-17 12:08 ]

Nimrod · May 17th, 2002, 01:15 PM

Hows this ?

Code:

Public Sub ParseNumbers()
    For Each c In Range("B15:B500")
        With c
           For Digit = 1 To Len(.Value)
                Num = Mid(.Value, Digit, 1)
                If IsNumeric(Num) Then NumStr = NumStr & Num
            Next
            Range("C" & .Row).Value = Val(NumStr)
            NumStr = ""
        End With
    Next c
End Sub

amna77 · May 17th, 2002, 01:35 PM

After running that macro, I got this following result.

16L2 = 162
16L3 = 163
16L4 = 164
16L5 = 165
16M6 = 166

but is there any way that I can get only 16, what ever comes after(text) L, then ignores it.
Many many Thanks for big help.

May 17th, 2002, 12:18 PM	#2
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	I'm a little confused in that you say the data is in RowB then you talk about the range being A15:A500... Anyway this code "moves" numbers from Row B to C. If you want it to be a "copy" instead of "move" then just take out the ".clear" from the following macro. .. cheers Code: Public Sub MoveNumbers() For Each c In Range("B15:B500") With c If IsNumeric(.Value) Then Range("C" & .Row).Value = .Value .Clear End If End With Next c End Sub

May 17th, 2002, 12:41 PM	#3
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	This version clears value but leaves formatting etc intact . Code: Public Sub MoveNumbers() For Each c In Range("B15:B500") With c If IsNumeric(.Value) Then Range("C" & .Row).Value = .Value .value = "" End If End With Next c End Sub Please let me know if this works out for you. _________________ NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions. Adieu,Nimrod [ This Message was edited by: Nimrod on 2002-05-17 11:42 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 17th, 2002, 11:54 AM	#1
amna77 Board Regular Join Date: May 2002 Posts: 251	Hi, Is there any way that you can Separate numbers from text. I have numbers and text in one columnB. Is there any macro that when I run, it should place numbers in column C. I have a data range from A:15 through A:500. Thanks in advnace.

May 17th, 2002, 12:43 PM	#4
amna77 Board Regular Join Date: May 2002 Posts: 251	In my column I have data like 13A 14L 8L I want to get separate only numbers from letters. when I run a macro then it should give me value 13 14 8 Is there any macro like that???

May 17th, 2002, 12:49 PM	#6
amna77 Board Regular Join Date: May 2002 Posts: 251	we have a seious of values as below 1 2 3 4L 5M 6R 10M 12 41 I want to sort them in decening order.

May 17th, 2002, 01:02 PM	#7
amna77 Board Regular Join Date: May 2002 Posts: 251	Yea formula will do it as well. Any formula how to separate numbers from text????????

May 17th, 2002, 01:35 PM	#10
amna77 Board Regular Join Date: May 2002 Posts: 251	After running that macro, I got this following result. 16L2 = 162 16L3 = 163 16L4 = 164 16L5 = 165 16M6 = 166 but is there any way that I can get only 16, what ever comes after(text) L, then ignores it. Many many Thanks for big help.