Thread: Extract number from text field

i have list of address of customers which may or maynot contains zip code.

eg:" C/O Ridhi Sidhi(Ganesham), 10 11, Basement Shardul Market, In Front Of Sbbj Bank, DT Jhunjhunu, PIN 333001

" ..... i want a formula to extract 6 digit zipcode wherever it is in the address field

it should extract just 6 digit number from all zip code

thanks in advance for your help

sailesh9630,

If, like in your example, the zip code is always the last 6 characters of the address, you can use the following formula (assumes you have a header and the addresses start in A2:
=RIGHT(A2,6)*1

Then copy down.

Hope that helps,
~tigeravatar

and its not compulsory that in data zip code is at end........
it may be in middle of address.....

if customers send address with error zip of 7 digit then it should not show any result

is there a possibility.

sailesh9630,

Can you post a sample workbook so we can get a better idea of how to help you?

~tigeravatar

Originally Posted by tigeravatar

sailesh9630,

Can you post a sample workbook so we can get a better idea of how to help you?

~tigeravatar

attaching file

Originally Posted by sailesh9630

attaching file

can you give your any chat account id so we can chat ...

i will be thankful for your help....

sailesh9630,

Because of how your data is setup, I created an addin to introduce a new formula. Here is the link for the GETZIP_Formula addin.

Download the addin and save it in the following folder:
C:\Documents and Settings\%username%\Application Data\Microsoft\AddIns

Then enable the add-in in Excel 2007/2010 by clicking on the office button -> Excel Options -> Add-ins -> Go -> and check "GETZIP_Formula"

Then you will be able to type in cell D2:
=IF(GETZIP(C2)=0,"",GETZIP(C2))

and copy down.

That formula has the following function:

Public Function GETZIP(strAddress As String) As Double
    
    Dim AddressPart() As String: AddressPart = Split(strAddress, " ")
    Dim PartIndex As Long, SearchIndex As Long
    For PartIndex = 0 To UBound(AddressPart)
        If Len(AddressPart(PartIndex)) = 6 _
        And IsNumeric(AddressPart(PartIndex)) Then
            GETZIP = AddressPart(PartIndex)
            Exit Function
        ElseIf Len(AddressPart(PartIndex)) > 6 Then
            For SearchIndex = 1 To Len(AddressPart(PartIndex))
                If Not IsNumeric(Mid(AddressPart(PartIndex), SearchIndex, 1)) Then
                    AddressPart(PartIndex) = Replace(AddressPart(PartIndex), Mid(AddressPart(PartIndex), SearchIndex, 1), vbNullString)
                End If
            Next SearchIndex
            If Len(AddressPart(PartIndex)) = 6 _
            And IsNumeric(AddressPart(PartIndex)) Then
                GETZIP = AddressPart(PartIndex)
                Exit Function
            End If
        End If
    Next PartIndex
    
End Function

Hope that helps,
~tigeravatar