Split an Address in one cell into multiple cells using a formula

**excelnovice2007** · 09-08-2010, 02:00 PM

Hi Guys,

Sorry if this has been asked before but i cannot seem to find the answer.

So here's the problem, i have a spreadsheet where the user enters an address in A1 as follows:

123 Made up Lane, Pretend City, Fairyland, England, ZZ12 5TT

and i need a formula to split this into multiple cells based on where the comma's are.

I can't use text to column thingy or a macro but need a formula because the address will change on multiple occasions. The problem is that each part of the address will not be constant between addresses and there may be 6 different 'sections' (parts seperated by commas), there may only be 3 or any combination in between.

Any help or pointers would be greatly appreciated.

Thanks

Martin

**ChemistB** · 09-08-2010, 02:18 PM

If you do Text to Columns and Choose Delimited and then choose the comma as your delimiter, it would work for you (no matter how many commas). Is there a reason you don't want to do that?

**avk** · 09-08-2010, 03:46 PM

Try this one: suppose your data in column A1

HTML Code:

**excelnovice2007** · 09-08-2010, 03:52 PM

Originally Posted by ChemistB

If you do Text to Columns and Choose Delimited and then choose the comma as your delimiter, it would work for you (no matter how many commas). Is there a reason you don't want to do that?

Hi

The reason i don't want to use the Text to Colums is because i then need to link the result from the forumla to many other cells and can't have the end user having to 'Text to Columns' each time a new address is entered.

I can't use a macro either unfortunately

**NBVC** · 09-08-2010, 04:04 PM

If your address is in A1:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100))

copied across as far as you need and down.

**excelnovice2007** · 09-08-2010, 04:15 PM

Originally Posted by NBVC

If your address is in A1:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100))

copied across as far as you need and down.

This is exactly what i was looking for, many thanks for your help it really is appreciated!

**avk** · 09-08-2010, 04:40 PM

suppose your data in coloum A1 :

HTML Code:

**onsid** · 02-14-2013, 09:56 AM

Originally Posted by avk

Try this one: suppose your data in column A1

HTML Code:

Hi there,
This is so elegant... I was looking for something like this :-)
Is there a way to paste the individual parts of the address/sentence horizontally instead of vertically?
Assuming the text is in A1, I am looking for a solution to break the text into as many columns as needed, placing the individual text fragments in B1, C1, D1, and so on...

Many thanks in advance

**Klickmeister** · 01-22-2015, 10:49 AM

Hi There,

an even more elegant and easy way to do this, is with that Add-In
https://www.youtube.com/watch?v=6RYD_I9PwX8

This Excel Add In splits addresses in seperate columns AND corrects (!!!) the data. Check out the video!