Splitting a Cell containg address

[jayw999]

My First post. Probably very simple for someone, sorry

I have a Column of addresses with all information in the Cell - First Line of the Address, 2nd line - in some cases, Town, Post Code. All separated by commas.

I want to create additional columns and place one element of the address in each new column

Is it simple or not?

[TMS]

Is it simple? In theory, yes. In practice, maybe not so. You can use Data | Text to Columns witha comma delimiter. The issues arise when you find that some people have longer addresses than others, so the elements can be mis-aligned.

But that's where you start.

Regards, TMS

[soberguy]

You can do this by counting the commas and then splitting.

=left(a1,Len(a1)-find(",",a1))

That will give you the first portion.

The rest would be dependent on the number of commas. There's another recent thread with the syntax, though it was finding parentheses.

[soberguy]

Here's the formula from the other thread:
=IF(LEN(B12)-LEN(SUBSTITUTE(B12,"(",""))=1,MID(B12,FIND("(",B12)+1,FIND(")",B12)-FIND("(",B12)-1),MID(B12,FIND("(",B12,FIND("(",B12)+1)+1,LEN(B12)-FIND("(",B12,FIND("(",B12)+1)-1))

So you want something like:
=if(Len(a1)-Len(substitute(a1,",",""))=4,mid(a1,find(",",a1)+1*,Len(a1)-find(",",a1,find(",",a1))),""**)

* Assumes no space after your first comma. Change to 2 if there is a space.

**Returns nothing when there isn't a second portion to address (suite, apartment, whatever).

Obviously, remove the * before using. Adjust for additional columns as needed.