Sometimes we may use space to separate texts to different groups in one cell, if we want to split this cell to multiple columns refer to space, we can implement this via ‘Text to Columns’ feature. But is there any way to split one cell to only two cells by the first space in the texts? In this free tutorial, we will share you some useful functions in excel to split cells per your demand.

Precondition:

In product list we use space to separate product properties.

Now we want split it to two parts in two different columns by the first space. See expectation below:

1. Split Cells to Two Columns by the First Space in Texts by Formula

Step1: In B2 enter the formula:

=LEFT(A2,FIND(" ",A2)-1)

Step2: Click Enter to get result. Verify that text before the first space is extracted and saved in B2 properly.

Step3: Drag the fill handle down till the end of the list. Verify that texts from the first space are displayed properly.

Step4: In C2 enter the below formula to extract the left texts after the first space.

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Step5: Click Enter to get result. Verify that texts are displayed properly.

Step6: Drag the fill handle down till the end of the list. Now texts are split to two columns by the first space properly.

2. Split Cells to Two Columns by the First Space in Texts with VBA Code

You can use a VBA Code that prompts the user to select a range of cells and a destination cell, and then splits the cells in the selected range to two columns based on the first space in the text.

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, go to “Insert” on the top menu and select “Module“.

Step3: Paste the below VBA code into the new module.

Sub SplitCellsByFirstSpace_ExcelHow()
    ' Prompt the user to select a range of cells
    Dim selectedRange As Range
    Set selectedRange = Application.InputBox("Select a range of cells:", Type:=8)
    
    ' Prompt the user to select a destination cell
    Dim destinationCell As Range
    Set destinationCell = Application.InputBox("Select a destination cell:", Type:=8)
    
    ' Insert the formula to split the text at the first space
    For Each cell In selectedRange
        cell.Offset(0, 2).Formula = "=RIGHT(" & cell.Address(False, False) & ",LEN(" & cell.Address(False, False) & ")-FIND("" "", " & cell.Address(False, False) & ",1))"
        cell.Offset(0, 1).Formula = "=LEFT(" & cell.Address(False, False) & ",FIND("" "", " & cell.Address(False, False) & ",1)-1)"
    Next cell
    
    ' Copy the formula to the destination cell and adjacent cell
    selectedRange.Offset(0, 1).Resize(selectedRange.Rows.Count, 2).Copy destinationCell
    
    ' Convert the formula to values in the destination cells
    destinationCell.Resize(selectedRange.Rows.Count, 2).Value = destinationCell.Resize(selectedRange.Rows.Count, 2).Value
End Sub

Step4: Press F5 to run the macro or click the “Run” button on the toolbar.

Step5: A dialog box will appear asking you to select a range of cells. Click and drag your mouse to select the range of cells you want to split.

Step6: Another dialog box will appear asking you to select a destination cell. Click on the cell where you want to place the split data.

Step7: The macro will split the data in the selected range into two columns based on the first space in the text, and place the split data into the destination cell and adjacent cells.

3. Video: Split Cells to Two Columns by the First Space in Texts

This video will demonstrate how to split cells to two columns by the first space in texts using Excel formulas and VBA code.

4. Related Functions

• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
• Excel FIND function
  The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
• Excel RIGHT function
  The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
• Excel LEN function
  The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…