CODEWITHSUNDEEP
excelexcel-formulavba
Nitin Jain
Nitin Jain

Reputation: 93

Extract Mobile numbers to other cells in excel

I have a unstructured data in some cells A1,A2,A3,A4 and want to extract mobile numbers in different cell 

    --------------------------(A)--------------------------
(1)   `ABCDEFG CFGHJKL 9810642882 9212451029 9818682274`
(2)   `ABCDERFT 9910077711 9811195125 9999966744`  
(3)   `ADFRTYU.9810851029 9818218521 9811056189`  
(4)   `ADFGT FTYUH 9873155802`

Result from cell A1:

      ----(A)---   ----(B)---  ----(C)---
(1)   9810642882   9212451029  9818682274

Upvotes: 0

Views: 1426

Answers (2)

JNevill
JNevill

Reputation: 50019

You can use Excel's built in "Text to Columns" functionality for this.

Highlight column A and go to Data>>Text To Columns. Choose "Delimited" and then "Space" as your delimiter. Click finish, and it will split the data by space and write it out to the adjacent columns.

For a VBA UDF you can use the super handy SPLIT() string method which will split a string by a delimiter to an Array. Then just pick off the position you need:

Public Function strtok(strIn As String, delim As String, token As Integer) As String
    'Split the <strIn> by the <delim> outputting the string at position <token>
    strtok = Split(strIn, delim)(token - 1)    
End Function

To use this in your worksheet:

=strtok(A1," ", 1)

Which will return the string at the first position of your A1 value when split by space. If you put this in B1 then you could use Column() to make it more dynamic so you can just drag the formula right and down:

=strtok($A1," ",Column()-1)

Here is an edit to the above funcion that will split by the delim, and then rebuild an array with just the numeric values from the split, then output the token requested from the numeric array:

Public Function strtok(strIn As String, delim As String, token As Integer) As String
    'Split the strIn by the delim outputting the string at position <token>

    Dim strArr As Variant 'array to split the initial string
    Dim numArr() As Double 'array to hold just numeric values
    Dim arrEl As Variant 'Element for the array

    'fill strArr
    strArr = Split(strIn, delim)

    'initial numArr()
    ReDim numArr(0 To 0)

    'Loop through each element in the strArr
    For Each arrEl In strArr
        If IsNumeric(arrEl) Then

            'resize the numArr to hold a new value
            If numArr(0) <> 0 Then ReDim Preserve numArr(0 To UBound(numArr) + 1)

            'write the value
            numArr(UBound(numArr)) = arrEl
        End If
    Next arrEl

    strtok = numArr(token - 1)

End Function

Upvotes: 2

tigeravatar
tigeravatar

Reputation: 26640

For a formula only solution, in cell B1 and copied over and down:

=TRIM(MID(SUBSTITUTE(TRIM(MID($A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},$A1&1234567890)),999))," ",REPT(" ",999)),999*(COLUMN(A1)-1)+1,999))

tigeravatar example for Nitin Jain

Upvotes: 3

Related Questions