• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 36120
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now

# I have an Excel spreadsheet with a column of street addresses.

This answer was rated:
I have an Excel spreadsheet with a column of street addresses. Most are in the usual format: "### XXXXX Street" . Is there a function/formula/procedure I can use to split the number from the street name and put them into two columns?
Thank you for your question, my name is Richard.

You can do it on the Space between the number and the street.

Try this function with the Example of the Address in A1

You would put one formula in B1 and the other in C1

=LEFT(A1, SEARCH(" ",A1,1))

=RIGHT(A1,LEN(A2)-SEARCH(" ",A1,1))

The " " is looking for a Space, but you can use any identifier.

If you have any difficulties at all, please do not hesitate to let me know so I can assist you further

Thank you
Customer: replied 4 years ago.

I replied, but now I don't see my follow-up question, explaining that the second formula doesn't seem to work. Did you get that Reply?

I did not get the reply sorry Greg.

The second formula had a reference to A2 still.

This is it corrected.

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))

Let me know if you have any difficulties please

Richard and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

Yep--error in your formula was what was wrong, and so now it works perfectly.

Thanks a bunch!

If you need anything else, please let me know

And please remember the Rating of my service.

Thank you

Customer: replied 4 years ago.

I just rated you Excellent. I normally put in a tip, but didn't because I have an outstanding question with this website's managers: I found two charges on my credit card statement that were larger than the tips I checked off, and want to know why before I tip again.

If you know the "answer" to that, let me know!

I do not sorry Greg, I am just an engineer, pity :(

But you should email your question to

[email protected]

They will resolve this for you

Let me know if you have any difficulties please

Customer: replied 4 years ago.
No difficulties--thanks!