Outline ·
[ Standard ] ·
Linear+
REGEX postcode problem. Help.
TSapache79
|
May 13 2020, 04:06 PM, updated 4y ago
|
New Member
|
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 86000but 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 75345How to get the postcode? Thank you.
|
|
|
|
Palindrome
|
May 15 2020, 09:51 PM
|
New Member
|
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 86000but 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 75345How to get the postcode? Thank you. And also postcode not always ends with trailing zeros...
|
|
|
|
nyem
|
May 15 2020, 11:04 PM
|
|
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
|
May 15 2020, 11:46 PM
|
New Member
|
Thank you. I've managed to fix it using this: CODE =REGEXEXTRACT(E4,".*(\d{5})\s")
|
|
|
|