excel - Extract number from column

Question

i have a lot of address data in (mostly) this format:

Karl-Reimann-Ring 13, 99087 Erfurt
Markttwiete 2, 23611 Bad Schwartau
Hüxstra?e 55, 23552 Lübeck
Bunsenstra?e 1c, 24145 Kiel

and my goal is to extract the zip code.

I copied a formula from a website, which i dont really understand:

=VERWEIS(9^9;1*TEIL(E2861&"#";SPALTE(2860:2860);6))

VERWEIS = LOOKUP,
TEIL = MID,
SPALTE = COLUMN

This formula seems to work 99% of the time, also for the ones above, but for some i get weird results.

Kurt-Schumacher-Stra?e 56, 55124 Mainz --> 44340
Kleine Früchtstra?e 6, 55130 Mainz --> 44346
Bahnstra?e 1, 55128 Mainz --> 44344

All with 'Mainz' are wrong and start with 44xxx

But when i increase the last argument from 6 to 7 it seems to work.

Do somebody know, how i can impove this formula to get always the correct zip code?

question from:https://stackoverflow.com/questions/65865938/extract-number-from-column

Answer 1

The problem is that the formula will return the last "number" which is constructed of 6 character strings starting at every character in the string.

The last substring that can be interpreted numerically (in the 55424 Mainz address) is actually 24 Mai. German Excel will parse that into 24 Mai 2021 which, as a number, will be 44340.

One modification you can make to your formula, to prevent that from happening, would be to add a comma after the zipcode. eg:

=LOOKUP(9^9;1*MID(SUBSTITUTE(A1;" ";",") & "#";COLUMN(2860:2860);6))

Another option would be to use FILTERXML where you can separate by spaces, and then return the last numeric node:

=FILTERXML("<t><s>" &SUBSTITUTE(A1;" ";"</s><s>") & "</s></t>";"//s[number(.) = number(.)][last()]")