Numbers Only

[eganeyar]

Hello,

Greetings!

May I ask for your assistance to help me get the data and remove all those after the numbers (symbol, letters, space, or any character).

Ex:

[TABLE="width: 342"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]+911111111 / abc@gmail.com[/TD]
[TD]+911111111[/TD]
[/TR]
[TR]
[TD]+911111111abc@gmail.com[/TD]
[TD]+911111111[/TD]
[/TR]
[TR]
[TD]+88888 abc@gmail.com[/TD]
[TD]+88888[/TD]
[/TR]
[TR]
[TD]+878787 +811111111[/TD]
[TD]+878787[/TD]
[/TR]
[TR]
[TD]+31312+811111111[/TD]
[TD]+31312[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Run this macro, your data must be in column A, the result will be in column B.
The format of column B must be Text.

Sub Ony_Number()
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        For j = 2 To Len(Cells(i, "A").Value)
            If InStr(1, "0123456789", Mid(Cells(i, "A"), j, 1)) = 0 Then
                Cells(i, "B").Value = Mid(Cells(i, "A").Value, 1, j - 1)
                Exit For
            End If
        Next
    Next
End Sub

 

[eganeyar]

Thhank you DanteAmor! Can you also include in the Macro that if numbers only, without symbol or any character after the number, it will also show the same data. The current macro result is showing blank.

Example:

Data Result
8989999 8989999

 

[alpadem]

Hi eganeyar,

=MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1))) then before press enter you should CTRL+SHIFT+ENTER

 

[Rick Rothstein]

Here is another macro that you can try (as with Dante's code, Column B must be formatted as Text)...

[table="width: 500"]
[tr]
	[td]Sub FirstNumbersOnly()
  Dim R As Long, LeftSymbol As String
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    LeftSymbol = Left(Cells(R, "A").Value, -(Cells(R, "A").Value Like "[!0-9]*"))
    Cells(R, "B").Value = LeftSymbol & Val(Replace(Cells(R, "A").Value, " ", "/"))
  Next
End Sub[/td]
[/tr]
[/table]

 

[eganeyar]

Thank you Rick! It works perfectly!

 

[jtakw]

Hi,

Formula option:

Book1
	A	B
5	Data	Result
6	+911111111 / [email]abc@gmail.com[/email]	911111111
7	+911111111abc@gmail.com	911111111
8	+88888 [email]abc@gmail.com[/email]	88888
9	+878787 +811111111	878787
10	+31312+811111111	31312
11	8989999	8989999
Sheet572

Cell Formulas
Range	Formula
B6	=-LOOKUP(1,-MID(A6,MIN(FIND({0,1,2,3,4}+{0;5},A6&1/17)),ROW($1:$99)))

 

[DanteAmor]

@[URL="https://www.mrexcel.com/forum/members/eganeyar.html"]eganeyar[/URL]

Sub Ony_Number()
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Cells(i, "B").Value = Cells(i, "A").Value
        For j = 2 To Len(Cells(i, "A").Value)
            If InStr(1, "0123456789", Mid(Cells(i, "A"), j, 1)) = 0 Then
                Cells(i, "B").Value = Mid(Cells(i, "A").Value, 1, j - 1)
                Exit For
            End If
        Next
    Next
End Sub