How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 37653
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

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))

Use that please.

Let me know if you have any difficulties please

Richard and 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!

Your welcome Greg.

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!