Extract Numeric characters

Will there always be a space after the 6th numeral, as in "600083 " or "600 083 "?

If so, this might work
=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")),7)

unless there are other numerals in the cell.

Thanks for your reply

Yes, may be but in rare cases, since the data received from multiple sources in different formats.

may be this


from here

http://www.mrexcel.com/forum/showthread.php?t=399606

sanrv1f said:

may be this

Excel Workbook
	A	B	C
39	A 600036	600036	600036
Sheet2

#VALUE!

Entered with Ctrl+Shift+Enter



from here

http://www.mrexcel.com/forum/showthread.php?t=399606

Click to expand...

Thanks sanrv1f,

But second formula is working fine. But the result includes Door No. also. I just need the zip code alone. It will be in 6 or 7 digits of three types (with or without space) like "600086", "600 086" & "600 086 ".

sanrv1f said:

may be this

Excel Workbook
	A	B	C
39	A 600036	600036	600036
Sheet2

#VALUE!

Entered with Ctrl+Shift+Enter



from here

http://www.mrexcel.com/forum/showthread.php?t=399606

Click to expand...

Could you pls help me with how to use udf which you have recommended the link http://www.mrexcel.com/forum/showthread.php?t=399606 for this issue? Pls help...

may be this, (this won't work, if the door no or any number other than PINCODE starts with 600)

the UDF can be used to extract all the number in a string (not a part of it),

=ZipCode(A1)

maheshmaxi said:

Thanks sanrv1f,

But second formula is working fine. But the result includes Door No. also. I just need the zip code alone. It will be in 6 or 7 digits of three types (with or without space) like "600086", "600 086" & "600 086 ".

Click to expand...

I am probably not thinking great, but would this work?<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> callit()<br><SPAN style="color:#00007F">Dim</SPAN> rCell<SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rCell<SPAN style="color:#00007F">In</SPAN> Selection<br>        rCell.Offset(, 1).Value = StripZip(rCell)<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> StripZip(rng<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> a<br>    <br>    a = Application.WorksheetFunction.Trim(rng.Value)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> InStrRev(a, Chr(32), -1, vbTextCompare) >= Len(a) - 5<SPAN style="color:#00007F">Then</SPAN><br>        <br>        StripZip = Mid(a, InStrRev(a, Chr(32), Len(a) - 4, vbTextCompare) + 1, 8)<br>    <SPAN style="color:#00007F">Else</SPAN><br>        StripZip = Mid(a, InStrRev(a, Chr(32), -1, vbTextCompare) + 1, 8)<br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN><br></FONT>



It would not catch trailing four digits after a space.

Mark

Seiya said:

=ZipCode(A1)

Code:

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

Click to expand...

Thanks Seiya, Its working great.