# Numbers Only

#### eganeyar

##### New Member
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:

 Data Result +911111111 / abc@gmail.com +911111111 +911111111abc@gmail.com +911111111 +88888 abc@gmail.com +88888 +878787 +811111111 +878787 +31312+811111111 +31312

<tbody>
</tbody>

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### DanteAmor

##### Well-known Member
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.

Code:
``````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

##### New Member
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

##### New Member
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

##### MrExcel MVP

Here is another macro that you can try (as with Dante's code, Column B must be formatted as Text)...
Code:
``````[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]``````

Last edited:

#### eganeyar

##### New Member
Thank you Rick! It works perfectly!

Hi,

Formula option:

Last edited:

#### DanteAmor

##### Well-known Member
@[URL="https://www.mrexcel.com/forum/members/eganeyar.html"]eganeyar[/URL]

Code:
``````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``````

Replies
7
Views
80
Replies
6
Views
757
Replies
0
Views
20
Replies
3
Views
289
Replies
2
Views
173