Extract number in the middle of text (check from right-to-left)

Hi all,

Need help on this problem. I need to extract postcode from address.
The postcode size is 5 character. and i think it is easy to check it from right to left because after the postcode, there will not be any number, only text.

The data :
1. NO. 106, JALAN LEP 3/4, TMN LESTARI PUTRA, 43300 SERI KEMBANGAN
2. B3-03-19 PANGSAPURI TANJUNG 1, JALAN MERDEKA VILLA 48000 AMPANG
3. A-2-3 PANGSAPURI SERI KAYAN USJ 2/1,47610 SUBANG JAYA SELANGOR DARUL EHSAN
4. J-7229 TAMAN MAJU 40675 JASIN

Expected outcome :
1. 43300
2. 48000
3. 47610
4. 40675

I have tried this solution but i still cant modify it to solve my problem.
Thank you for your help and attention

For the wide variety of strings you've presented, I would write a brand new User Defined Function (UDF) to make this simple do via formula. First we add the new function to your workbook:

Please Login or Register  to view this content.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook (*.xlsm)

The function is installed and ready to use.

With the strings in column A, you could put this formula in B1, then copy down:

=LASTNUM(A1)

Originally Posted by diywho

....after the postcode, there will not be any number, only text...

if so, one way:

=MID(A1,MATCH(10,INDEX(--MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),0))-4,5)

Haseeb, that is an awesome formula.