Bhubon Costa
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.
Step 1: Insert FIND Function find_text Argument
- To find the first space position number, type the find_text argument.
=FIND(" ",
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)
- Then, press Enter to see the position of the first space from the left. It results in 8 as the image is shown below.
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
- Insert [num_chars] argument using the FIND function value of Step 2.
=LEFT(B5,FIND(" ",B5)-1)
- Therefore, it will result in the Order ID before the first space.
- Use the AutoFill Handle Tool to auto-fill the cells.
Step 4: Enter FIND Function to Find Space
- As previously, type the following formula to find the space from the left.
=FIND(" ",B5)
- As a consequence, it will display as 8, because the first space is in the 8th position from the left.
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)
- As a result, it will result in 12 as the first space is 12th characters away from the right side.
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
- Secondly, use the result from Step 5 as the [num_chars] argument of the RIGHT function.
=RIGHT(B5,LEN(B5)-FIND(" ",B5))
- Finally, press Enter to see the result from the right side and it will show the Customer Name.
- Then, apply the AutoFill Handle Tool to fill the required cells.
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.
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,
Thank you very much for your appreciation. Follow our website ExcelDemy for other problems, and hope you will always get the best solutions.