Extract numbers and string after

[matija385]

Hi all,

I need help - I have addresses and I need to extract 5 digit number and string that comes after those digits.

For ie.:
Sarajevska 27, 10000 Zagreb - I need 10000 Zagreb
Stari hrast 45, 10431 Sveta nedelja, rakitje - I need 10431 Sveta nedelja, rakitje
Gustava Krkleca 16/1, Srdoči, 51000 Rijeka - I need 51000 Rijeka
Kolombera 75/A, 52470 Umag (Umago) - I need 52470 Umag (Umago)
GORNJE LADANJE,VATROSLAVA LISINSKOG 4, 42207 VINICA - I need 42207 VINICA

I have no idea how to do this with VBA, if someone is willing to help, i would be more than grateful.

Thnx,

Kind regards,
Matija

 

[MickG]

Data in column "A" , Results in column "B".

[COLOR="Navy"]Sub[/COLOR] MG27Feb31
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, ",")
    Dn.Offset(, 1).Value = Sp(UBound(Sp))
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AlKey]

Try this
Assuming data starts in cell A1
Enter formula in B1 and copy down

[B]=MID(A1,MATCH(1,INDEX(-MID(A1,COLUMN(1:1),1),0))-4,50)[/B]