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?

Ron Rosenfeld · Accepted Answer · 2021-01-24 01:40:10Z

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()]")

answered Jan 24, 2021 at 1:40

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harun24hr Over a year ago

FILTERXML() will be best choice.

AnilGoyal Over a year ago

upvoted for filterxml use. Can you please explain a bit more of this syntax

Ron Rosenfeld Over a year ago

@AnilGoyal see this from jvdv and also the w3schools xPath tutorial. The formula before the xPath is merely to construct the xml.

Collectives™ on Stack Overflow

Extract number from column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related