Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Removing Extra Spaces from an Address

0

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!

SVD

excel
MS Office Version: 2013
posted by: SVDEXCEL (rep: 2) on Wed Jul 27, 2016 at 4:35 pm
Answer
Discuss

Answers

0
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))))
posted by: don (rep: 1989) on Wed Jul 27, 2016 at 5:25 pm
Discuss

Discussion

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