CODEWITHSUNDEEP
excelexcel-formulaexcel-2007vba
pan dong
pan dong

Reputation: 61

How to extract the six digit number in a cell string?

I have a column with data:

New York is really nice, 456983 Good food
Beijing is awesome 678932, Nice culture.
... and so on

I want to extract the six digit numbers and place it in a column beside the original column.

There are n rows.

Is there any way to do this with a formula, without VBA?

Upvotes: 4

Views: 21735

Answers (4)

gavin
gavin

Reputation: 325

This works for me. This will look for all digits in the string. - You can of course add limitations.

Sub numberExtract()
    x = ActiveCell
    Dim valIs As String
    Dim a As String
    For i = 1 To Len(x)
        a = Mid(x, i, 1)
        If IsNumeric(a) Then
            valIs = valIs & a
        End If
    
    Next i
    MsgBox valIs
End Sub

Upvotes: 0

barry houdini
barry houdini

Reputation: 46371

This formula works for both of your examples:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),6)

Upvotes: 2

Carol
Carol

Reputation: 471

You can use this, enter as an array formula:

=MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),SUMPRODUCT(--ISNUMBER(MID(A2,ROW($A$1:$A$200),1)+0)))

Upvotes: 0

cbasah
cbasah

Reputation: 538

Without VBA, you can use the following Excel formula assuming A1 is the cell containing the text.

=MID(A1,FIND("------",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","-"),"1","-"),"2","-"),"3","-"),"4","-"),"5","-"),"6","-"),"7","-"),"8","-"),"9","-")),6)

Upvotes: 6

Related Questions