0

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?

nitsuj1001
  • 51
  • 1
  • 1
  • 10

1 Answers1

3

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()]")
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • `FILTERXML()` will be best choice. – Harun24hr Jan 24 '21 at 02:06
  • upvoted for `filterxml` use. Can you please explain a bit more of this syntax – AnilGoyal Jan 24 '21 at 15:48
  • 1
    @AnilGoyal see [this](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml) from jvdv and also the [w3schools xPath tutorial](https://www.w3schools.com/xml/xpath_intro.asp). The formula before the xPath is merely to construct the `xml`. – Ron Rosenfeld Jan 24 '21 at 18:12