eganeyar

New Member
Joined
Feb 6, 2015
Messages
22
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:

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

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Feb 6, 2015
Messages
22
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

New Member
Joined
Oct 9, 2018
Messages
15
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
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Formula option:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Data</td><td style=";">Result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">+911111111 / abc@gmail.com</td><td style="text-align: right;;">911111111</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">+911111111abc@gmail.com</td><td style="text-align: right;;">911111111</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">+88888 abc@gmail.com</td><td style="text-align: right;;">88888</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">+878787 +811111111</td><td style="text-align: right;;">878787</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">+31312+811111111</td><td style="text-align: right;;">31312</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">8989999</td><td style="text-align: right;;">8989999</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet572</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=-LOOKUP(<font color="Blue">1,-MID(<font color="Red">A6,MIN(<font color="Green">FIND(<font color="Purple">{0,1,2,3,4}+{0;5},A6&1/17</font>)</font>),ROW(<font color="Green">$1:$99</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
@[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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,131
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top