How to Split Column by First Space in Excel: 6 Easy Methods

Method 1 – Insert FIND Function find_text Argument

• Find the first space position number; type the find_textÂ argument.
`=FIND(" ",`

Method 2- Enter FIND Function within_text Argument

• The first space in cell B5; write the within_text argument with the following formula.
`=FIND(" ",B5)`

• Press Enter to see the position of the first space from the left. It results in 8, as the image is shown below.

Method 3 – Apply LEFT Function to Split Column by First Space

• Write the LEFT function text argument as B5 with the following formula.
`=LEFT(B5`

• Insert [num_chars] argument using the FIND function value of Step 2.
`=LEFT(B5,FIND(" ",B5)-1)`

• It will result in the Order ID before the first space.

• Use the AutoFill Handle Tool to auto-fill the cells.

Method 4 – Enter FIND Function to Find Space

• Type the following formula to find the space from the left.
`=FIND(" ",B5)`

• It will display as 8 because the first space is in the 8th position from the left.

Method 5 – Use LEN Function

• Use the LEN function to count the total number of characters, and subtract the result of the FIND function to find the location of the first space from the right.
`=LEN(B5)-FIND(" ",B5)`

• It will result in 12 as the first space is 12th characters away from the right side.

Method 6 – Apply RIGHT Function to Split Column by First Space

• Enter the RIGHT function text argument to extract the value from cell B5 with the following formula.
`=RIGHT(B5`

• Use the result from Step 5 as the [num_chars] argument of the RIGHT function.
`=RIGHT(B5,LEN(B5)-FIND(" ",B5))`

• Press Enter to see the result from the right side, and it will show the Customer Name.

• Apply the AutoFill Handle Tool to fill the required cells.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

1. This is a really excellent tutorial.
Very clearly laid out.
Thank you so much.
I had been given an excel spreadsheet of library books with author and title in a single cell. The author was always followed by a colon but the title also could contain additional colons. Thus the “tesxt to columns” function split the title field into multiple columns that I then had to re-merge.
Your solution solved the problem for me – Thank you so much!

• Hi DAVID,