CODEWITHSUNDEEP
excelvbaexcel-formula
Bardia.Alavi
Bardia.Alavi

Reputation: 23

Extract from string delimited by one or more commas

I am trying to extract text after the first comma and potentially the remaining commas.

My text is addresses and some have lot numbers, units numbers ... I don't need.
I compare my addresses to a database with a specific format.

For example: 37 neptune Street, REVESBY NSW 2212

The formula:

=RIGHT(B2,FIND(",",B2,1)-1)

returns REVESBY NSW 2212. This is what I want.

If I have LADY GOWRIE CHILD CARE CENTRE, Ground Floor 1, 16 Spears Drive, DUBBO NSW 2830.
I get Spears Drive, DUBBO NSW 2830.
The formula is giving me everything to right of the second comma.

I want to extract data separately before each comma. So I can have Street Number, Street Name, suburb, Post Code.

My data for every cell is a bit different.

Upvotes: 0

Views: 5264

Answers (1)

Solar Mike
Solar Mike

Reputation: 8375

So, just to show a method using functions while Peh shows a better method:

enter image description here

The trim() removes leading or trailing spaces, The +1 is to not pick up the comma after the find() gets its position.

By using find() to drive a second find() you can split out all of the parts, but it gets cumbersome... Left for you to try...

Upvotes: 1

Related Questions