Extract number from random text string without fixed format

Question

I want to extract 10 digit mobile form random string in cell A1 which has text as well as mobile numbers some has one mob ile other has two or maybe three mobile numbers .All mobile number to be saved in different coloumns.Only excel sheet formula is required

You might want to anonymise that data. – Wodin Apr 28 '16 at 15:52 — Wodin, Apr 28 '16 at 15:52
Have you tried to do this yourself? – Mark Chorley Apr 28 '16 at 16:16 — Mark Chorley, Apr 28 '16 at 16:16

score 1 · Accepted Answer · answered Apr 28 '16 at 18:24

First of all, you will need to create a named range. The purpose of the named range is to normalize and split the data by space so that it can be read by other formulas without having to type that out each and every time.

First, put your data in column A starting in row 1 (as shown in your sample data image). Then create a named range with a name of SplitString and define it with this formula:

=INDEX(TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($A1,"."," "))," ",REPT(" ",999)),999*(ROW($1:$10)-1)+1,999)),)

Note the ROW($1:$10). The 10 in that is a guess that the strings will never have more than 10 entries to evaluate in a single cell. This is consistent with your sample data where the cell with the most entries is ROHTAK (BUILDER) 7777777777 PAL 6666666666 which has 5 entries to be evaluated. If you need to increase the number, just increase the 10 to be a higher number.

Then in cell B1 and copied over and down, use this formula which utilizes the SplitString named range that has been defined:

=IFERROR(IF(AND(ISNUMBER(--$A1),LEN($A1)=10,COLUMN(A1)=1),--$A1,--INDEX(SplitString,MATCH(1,INDEX((COUNTIF($A1:A1,SplitString)=0)*(LEN(SplitString)=10)*(ISNUMBER(--SplitString)),),0))),"")

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

I would try using regular expressions as mentioned in this answer : https://stackoverflow.com/a/22542835/2068595

On the face of it, I would look for this regex [0-9]{10} (meaning 10 consecutives characters from 0 to 9) in your column.

edited May 23 '17 at 10:33

Community

1
1

answered Apr 28 '16 at 15:52

Laurent Mesguen

354
2
6
22

score 0 · Answer 3 · answered Apr 28 '16 at 16:25

With data in column A run this short macro:

Sub numbersss()
    Dim N As Long, L As Long, K As Long
    Dim i As Long, j As Long, t As String
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For j = 1 To N
        t = Cells(j, 1).Text
        L = Len(t)
        For i = 1 To L
            If Mid(t, i, 1) Like "[0-9]" Then
            Else
                Mid(t, i, 1) = " "
            End If
        Next i
        ary = Split(Application.WorksheetFunction.Trim(t), " ")
        K = 2
        For Each a In ary
            If Len(a) = 10 Then
                Cells(j, K) = "'" & a
                K = K + 1
            End If
        Next a
    Next j
End Sub

For example:

If someone posts a pure formula solution, please ignore this post.

Extract number from random text string without fixed format

3 Answers3