Text to Columns – First Space Only

 Feb 02, 2016

I was recently asked how the street number and street name could be split into two cells (eg 344 Queen Street to 344 in one cell and Queen Street in another) using Microsoft Excel. The Text to Columns Wizard would split it into three cells because it would split the data at the position of both spaces.

The answer lies with Text Functions. There are 24 functions in the Text category. The ones we need in this case are:

=LEFT() Returns the leftmost characters from a text value

=FIND() Finds one text value within another (case-sensitive)

=RIGHT() Returns the rightmost characters from a text value

=LEN() Returns the number of characters in a text string

Number: To find the street number we need to find the position of the first space and subtract one to determine how many characters from the left to display.

Name: To find the street name we need to determine the length of the string of text, find the position of the first space and deduct the first number from the second to determine how many characters from the right to display.

For example:

  A B C
1 344 Queen Street =LEFT(A1,FIND(” “,A1)-1) =RIGHT(A1,LEN(A1)-FIND(” “,A1))

Would give you:

  A B C
1 344 Queen Street 344 Queen Street

If you are interested in more Text Functions as well as other types of functions, see New Horizons' Excel training courses.

How do your Excel skills stack up?   

Test Now  

About the Author:

Ben Kirk  

With over 16 years of experience working as a Desktop Applications specialist for a number of large education services providers, Ben is one of New Horizons most skilled and dynamic instructors. With his Advanced Diploma of Business Skills alongside his practical experience and expertise, Ben is able to provide insight and guidance to students at all skill levels across the entire Microsoft Office suite.

Read full bio
top