Extract numbers and string after

matija385

Board Regular
Joined
Sep 17, 2014
Messages
77
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Data in column "A" , Results in column "B".
Code:
[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
 
Upvote 0
Try this
Assuming data starts in cell A1
Enter formula in B1 and copy down
Code:
[B]=MID(A1,MATCH(1,INDEX(-MID(A1,COLUMN(1:1),1),0))-4,50)[/B]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,637
Members
452,663
Latest member
MEMEH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top