0

I have an Excel challenge that has been giving me issues. If you see attached I was able to successfully separate the "senders address" column into an address, state, city, zip format. The problem I am having is the "receivers address" column. I tried using the text to columns feature but the spacing throws off the data drastically.

Does anyone know a better solution on how to potentially separate the receivers address column into the address, city, state, and zip format?

See example here.

Excel Sheet Image

Community
  • 1
  • 1
ForrestFairway
  • 133
  • 1
  • 11
  • This looks like GSA data. Are the receivers restricted to US destinations or could they be international? Suggest you use [tag:vba] [tag:regex]. –  Jul 12 '17 at 15:17
  • Have you looked at the data closely? There may be zero-length space characters hiding within which can be used for TextToColumns. –  Jul 12 '17 at 15:19
  • is there any format that you have observed common with all the addresses? – Gowtham Shiva Jul 12 '17 at 15:30
  • What is a zero-length space character and what should I be looking for? @jeeped – ForrestFairway Jul 12 '17 at 15:46
  • unfortunately I have not been able to find anything common within these addresses @GowthamShiva – ForrestFairway Jul 12 '17 at 15:47
  • In S2 put `=unicode(mid(q$2, row(1:1), 1))` and fill down. This returns the unicode for each character. Look for numbers that are not 32 (conventional space), not 48 to 57 (numbers) and not 65 to 90 (uppercase letters). If you cannot find any then your task is impossible without a cross-lookup based on zip code. –  Jul 12 '17 at 15:53

1 Answers1

0

I can't help with separating the street address and city (unless you are lucky and there are zero-width spaces in your data, as mentioned in the comments). But we can get your states and zip codes by adapting the formulas in this answer:

enter image description here

Helper column:

=LEFT(A2,FIND("@",SUBSTITUTE(A2," ","@",(LEN(A2)-LEN(SUBSTITUTE(A2," ","")))/LEN(" ")))-1)

Street Address + City:

=LEFT(B2,LEN(B2)-2)

State:

=RIGHT(B2,2)

Zip Helper:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))

Zip:

=IF(LEN(E2)>5,LEFT(E2,5)&"-"&RIGHT(E2,LEN(E2)-5),E2)
HaveSpacesuit
  • 3,572
  • 6
  • 40
  • 59