Excel 2007 : How to split text, number and a letter into 3 columns?

[Tofsy]

I have read every single thread in internet and this forum and haven't found any answer to this question.
I have a large list of companies with their addresses, for example:

High Mountain 3
Little Italy 445
West Virgina 23 a

I would like to split them apart to get every part of the address into 3 columns (West Virginia / 23 / a). The problem is that I can't use functions LEFT, RIGHT or MID because the length of the words is different.

Thanks for your solutions!

[sweep]

Hi,

Try this:

Data > Text to columns > Delimited > Space > Finish

[Tofsy]

Hi, i forgot to say that I have already tried it, but the problem is that some addresses have more words (example: Northington or Little Italy). I would like to have 3 columns: Address, number, additional letter. In your case I get everything messed up.

[Fotis1991]

Hi

Wuth your addresses in Column A, in B1, put this formula

=LEFT(A1;FIND(" ";A1;FIND(" ";A1;1)+1)-1)

In C1 the Array formula(Ctr+Shift+Enter)

=SUM((MID(0&A1;LARGE(IF(ISNUMBER(MID(0&A1;ROW($1:$256);1)*1);ROW($1:$256);1);ROW($1:$256));1)*1)*10^(ROW($1:$256)-1))

In D1, this also Array Formula

=TRIM(MID(A1;MAX(ISNUMBER(MID(A1;COLUMN(1:1);1)*1)*COLUMN(1:1))+1;2^15))

Copy down.

[Søren Larsen]

Without array formula you could do this:

In B1:

=LEFT(A1;LEN(A1)-IF(AND(C1<>"";D1<>"");LEN(C1)+LEN(D1)+2;IF(OR(C1<>"";D1<>"");LEN(C1)+LEN(D1)+1;A1)))

In C1:

=IF(ISNUMBER(MID(A1;FIND("@";SUBSTITUTE(A1;" ";"@";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))+1;256)*1);MID(A1;FIND("@";SUBSTITUTE(A1;" ";"@";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))+1;256)*1;MID(A1;FIND("@";SUBSTITUTE(LEFT(A1;LEN(A1)-LEN(D1)-1);" ";"@";LEN(LEFT(A1;LEN(A1)-LEN(D1)-1))-LEN(SUBSTITUTE(LEFT(A1;LEN(A1)-LEN(D1)-1);" ";""))));LEN(A1)-FIND("@";SUBSTITUTE(LEFT(A1;LEN(A1)-LEN(D1)-1);" ";"@";LEN(LEFT(A1;LEN(A1)-LEN(D1)-1))-LEN(SUBSTITUTE(LEFT(A1;LEN(A1)-LEN(D1)-1);" ";""))))-LEN(D1)*1)*1)

In D1:

=IF(ISNUMBER(MID(A1;FIND("@";SUBSTITUTE(A1;" ";"@";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))+1;256)*1);"";MID(A1;FIND("@";SUBSTITUTE(A1;" ";"@";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))+1;256))

Copy down

The above only works if there is only 1 letter in the end of the address. It should work no matter how many words are in the streetname.

[Tofsy]

Guys thank you very much!

@Fotis1991: Your function doesn't seem to work because it could work only with streets, that contain 2 words. Beside that, i always get 0 instead of number of the street.

@Søren Larsen: Your function works perfectly! As you said it works only with addresses, that have a or b in the end and it doesn't work if they have a b in the end (it works with a/b though!). In that case I can go through manually and just correct the A column instantly!

Thanks again for your effort, I got my answer.

[Fotis1991]

Hi

Nice that you found your solution. But it's different when a formula is better than another and different that to say that doesn't works..

@Fotis1991: Your function doesn't seem to work because it could work only with streets, that contain 2 words. Beside that, i always get 0 instead of number of the street.

1) Yes, first formula, is for 2 words only(as your example)

2) Formula with the numbers, i think works ok