Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 REGEX postcode problem. Help.

views
     
TSapache79
post May 13 2020, 04:06 PM, updated 4y ago

New Member
*
Junior Member
46 posts

Joined: Feb 2011
Hi coders, can you help on this.. managed to get the postcode from address using this regex.

Abu bin Ali
No 75 Jln Bawang 1 Taman Makmur 86000 Kluang Johor
018-3453467

CODE
=REGEXEXTRACT(E5,"(\d{5})\s")


return 86000

but if the address have same 5 numbers it will return the first 5 numbers.

Abu bin Ali
No 75345 Jln Bawang 1 Taman Makmur 86000 Kluang Johor
018-3453467

return 75345

How to get the postcode?

Thank you.
Palindrome
post May 15 2020, 09:51 PM

New Member
*
Validating
30 posts

Joined: Feb 2020
QUOTE(apache79 @ May 13 2020, 04:06 PM)
Hi coders, can you help on this.. managed to get the postcode from address using this regex.

Abu bin Ali
No 75 Jln Bawang 1 Taman Makmur 86000 Kluang Johor
018-3453467

CODE
=REGEXEXTRACT(E5,"(\d{5})\s")


return 86000

but if the address have same 5 numbers it will return the first 5 numbers.

Abu bin Ali
No 75345 Jln Bawang 1 Taman Makmur 86000 Kluang Johor
018-3453467

return 75345

How to get the postcode?

Thank you.
*
And also postcode not always ends with trailing zeros...
nyem
post May 15 2020, 11:04 PM

Enthusiast
*****
Senior Member
749 posts

Joined: Jan 2007


Not sure if negative lookahead is supported in worksheet

This should work on Javascript
CODE

/\b(\d{5}(?!.*\d{5}))\b/g


Tested at https://regexr.com/54mrv


TSapache79
post May 15 2020, 11:46 PM

New Member
*
Junior Member
46 posts

Joined: Feb 2011
Thank you. I've managed to fix it using this:

CODE
=REGEXEXTRACT(E4,".*(\d{5})\s")


 

Change to:
| Lo-Fi Version
0.0168sec    0.09    5 queries    GZIP Disabled
Time is now: 29th March 2024 - 06:03 AM