Finding several post codes in text (all in one cell)

Question

Im trying to write a script that loops through all cells in a column and checks each cell's text if there are two or more post codes in there. If it finds two or more post codes, it needs to highlight the whole row. Cells are filled with adress, like Street of hard-workers 2/12, 77-200, London. I have something like that below, but it does not include situation in which street's name is written with -.

Any ideas? :(

sub find_postcode()

For Each cell In Range("D2", Range("D" & Rows.Count).End(xlUp))
    If Len(cell) - Len(Replace(cell, "-", "")) > 1 Then
        cell.EntireRow.Interior.Color = 65535
    End If
Next cell

End sub

What would you describe as the pattern for valid postcodes? And do you have samples where two or more postcodes are used? How are they refered to in-cell? Maybe there is a delimiter? — JvdV, Feb 14 '20 at 12:22
As @JvdV indicates, you need to supply a comprehensive pattern for all valid post-codes, as well as examples of where you think things might get confused. And if this has to be internationally aware, the patterns will be even more complex, as many countries have their own specified formatting. — Ron Rosenfeld, Feb 14 '20 at 12:59
You're looking for postcodes, but your example doesn't include a postcode? — Darren Bartrup-Cook, Feb 14 '20 at 13:07
Thank you for a hint. Post codes will always have the following pattern: numernumber-numbernumbernumber, like 99-999 or 00-000. Only numbers. It is crucial that the script finds only cells consisting of more than one post code. Im reading link and will try to find out something :) — SharpAnalysis, Feb 17 '20 at 11:22
In that case you could also compare with `Like` operator, for example: `"*##-###*##-###*"` but to really be sure `RegEx` would be better as per @Pᴇʜ. To prevent postcodes to be part of a larger substring, I think you'll have to do some pattern like [`\b\d{2}-\d{3}(?!\w)`](https://regex101.com/r/WQ7xiI/3). Test if your string has any matches, if so then `Execute` the regular expression and count the results e.g.: `If RegEx.Execute(str).Count > 1 Then....` — JvdV, Feb 17 '20 at 12:48

Pᴇʜ · Answer 1 · 2020-02-14T12:37:11.643

You could use Regular Expressions (How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops) with a pattern like ([0-9]+)-([0-9]+) (see https://regex101.com/r/gjPLqu/1/) to find only dash - delimited numbers in your address.

If you get a match with that pattern then highlight your cell.

If you have also house numbers like 1-3 like in Another Street 1-3, 77-200, Paris this pattern will recognize them false positive. Then you need to adjust your pattern to something more concrete like , ([0-9]+)-([0-9]+), so it finds only the ones between commata.

Finding several post codes in text (all in one cell)

1 Answers1