Extract Numeric characters

[maheshmaxi]

Hi,

I have a column contains Postal Adress in more than 5000 rows. Column contains Door Number, Area, City and Zip code. I need to separate "Zip code" alone in next coulmn. Zip code (of India) will be in six digits like "600083" also some cases contain space in middle of zip code like "600 083" (after 3 digits). Is there any way to do this without doing cut & paste? Pls help me...

 

[tusharm]

I would modify the pattern to \b(\d{3}\s?\d{3}) *$ This will ensure that the code does not find a 6 digit door number.

=ZipCode(A1)

Function ZipCode(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\b(\d{3}\s?\d{3})\b"
    If .test(txt) Then
        ZipCode = .execute(txt)(0).submatches(0)
    End If
End With
End Function

 

[Legacy 68668]

I would modify the pattern to \b(\d{3}\s?\d{3}) *$ This will ensure that the code does not find a 6 digit door number.

I just didn't know about Zip code in India, if it comes in the middle or at the end.

If it is always at the end then
I would change it to

"\b(\d{3}\s?\d{3})$"

No "*" and no space before $

 

[tusharm]

The pattern has to allow for the OP's requirement that there *may* be trailing spaces after the zip code.

In any case, this is splitting hairs, especially since the OP, happy with your original post, seems to have disappeared. {grin}

​

I just didn't know about Zip code in India, if it comes in the middle or at the end.

If it is always at the end then
I would change it to

"\b(\d{3}\s?\d{3})$"

No "*" and no space before $

 

[maheshmaxi]

The pattern has to allow for the OP's requirement that there *may* be trailing spaces after the zip code.

In any case, this is splitting hairs, especially since the OP, happy with your original post, seems to have disappeared. {grin}

Hi Tusharam,

Indian Zip code is 6 digit (without space) Eg: 600083. But my address list contains zip codes like " 600083", "600083 ", "600 083" spaces may exist start of zip code, end of zip code and middle 'after 3 digits' (not after 1 digit and so on)

As of now I am using Seiya's 1st original post (mentioned below). And I don't find any error in output since the data is more than 10,000. And I am not aware of Macro codings. Should I need to amend the below code? Pls advise...

Function ZipCode(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "\b(\d{3}\s?\d{3})\b"
If .test(txt) Then
ZipCode = .execute(txt)(0).submatches(0)
End If
End With
End Function