Removing Extra Spaces from an Address


Hello everybody,

I'm trying to extract (split) information from one (1) column that has the complete adrress to (4) different columns:

  1. House number and street name
  2. City
  3. State
  4. Postal Code

I was able to extract columns 1, 2 and 4, but the column 3 is given me a trouble, because is havin an extra space at the beginning, Could some one check the formula and help me to find the error.

Example: 156 Windsor Drive, Arlington Heights, IL 60004

The formula that I used was: =MID(A3,FIND(",",A3,FIND(",",A3,1)+1)+1,FIND(" ",A3,FIND(" ",A3)))

But the answer is:   " "IL (" " space at the beginning)

Please see the example in the spreadsheet attached

Thanks a lot for the time and help!




Selected Answer

Put TRIM() around it and that will remove the extra space.

Your new formula would be this:

=TRIM(MID(A3,FIND(",",A3,FIND(",",A3,1)+1)+1,FIND(" ",A3,FIND(" ",A3))))


Hi Don, Thank you very much for your time and help. Have yourself a wonderful rest of the day! Sincerely, SVD
SVDEXCEL (rep: 2) Jul 28, '16 at 2:03 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login