How to Split Column by First Space in Excel (with Easy Steps)

With the text to column tool in Excel, you may easily split cell information separated by commas, spaces, or other delimiters. You might be interested in learning how to separate columns in Excel using formulas. In this tutorial, we will show how to split column in Excel by First space.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Easy Steps to Split Column by First Space in Excel

In the example below, we’ve included a data collection with many Customer Names and their Order IDs in the same cell. We wish to split or separate the Order ID and Customer Names into two independent columns for obvious reasons.

First, we’ll use the FIND function to find the first space position number, followed by the LEFT function to obtain the result before the first space from the left. Later on, we will utilize the LEN function in combination with the FIND function to locate the first space from the right. The RIGHT function will then be used to extract the value from the right side before the first space.

Sample Data

Step 1: Insert FIND Function find_text Argument

  • To find the first space position number, type the find_text argument.
=FIND(" ",

Easy Steps to Split Column by First Space in Excel


Step 2: Enter FIND Function within_text Argument

  • As we are searching the first space in cell B5, write the within_text argument with the following formula.
=FIND(" ",B5)

Easy Steps to Split Column by First Space in Excel

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

Easy Steps to Split Column by First Space in Excel

Read More: Split Column in Excel by Word (5 Effective Ways)


Step 3: Apply LEFT Function to Split Column by First Space

  • Firstly, write the LEFT function text argument as B5 with the following formula.
=LEFT(B5

Easy Steps to Split Column by First Space in Excel

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

Easy Steps to Split Column by First Space in Excel

  • Therefore, it will result in the Order ID before the first space.

Easy Steps to Split Column by First Space in Excel

Easy Steps to Split Column by First Space in Excel


Step 4: Enter FIND Function to Find Space

  • As previously, type the following formula to find the space from the left.
=FIND(" ",B5)

Easy Steps to Split Column by First Space in Excel

  • As a consequence, it will display as 8, because the first space is in the 8th position from the left.

Easy Steps to Split Column by First Space in Excel


Step 5: Use LEN Function

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

Easy Steps to Split Column by First Space in Excel

  • As a result, it will result in 12 as the first space is 12th characters away from the right side.

Sample Data


Step 6: Apply RIGHT Function to Split Column by First Space

  • Firstly, enter the RIGHT function text argument to extract the value from cell B5 with the following formula.
=RIGHT(B5

Sample Data

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

Sample Data

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

Sample Data

  • Then, apply the AutoFill Handle Tool to fill the required cells.

Sample Data

Read More: Excel Formula to Split One Column into Multiple Columns (4 Examples)


Conclusion

To conclude, I hope this article has given you some useful information about how to split columns in excel by the first space. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

2 Comments
  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!

Leave a reply

ExcelDemy
Logo