You can use the Text to Columns feature with all versions of Excel.

Formula to split text in excel by space. The first name starts with the first character from the left and ends at the ninth character the second space. Split string by delimiter or pattern separate text. C5 LEFT B5FIND_ B5 - 1 left side D5 RIGHT B5LEN B5 - FIND_ B5 right side.

There are several Excel functions that can be used to split and manipulate text within a cell. This time well change everything to Text so Excel. The formula extracts nine characters starting from the left.

This method will introduce Kutools for Excels Split Cells utility to split text strings into multiple rows or columns by specified delimiter in Excel easily. Well deal with this later. In this example the goal is to split a text string at the underscore _ character.

This formula involves nesting SEARCH to find the second instance of space from the left. The Excel RIGHT function returns a substring a specified number of the characters from a text string starting from the rightmost characterThe syntax of the RIGHT function is as below RIGHT text num_chars. LEFTA4A6FIND A4A6 RIGHTA4A6LENA4A6-FIND A4A6 You cant write dynamic array formulas in an Excel table so you can name the range of cells containing the names via OFFSET.

It can also be a valid cell reference within a workbook. Text to Columns feature for splitting text When splitting text in Excel the Text to Columns is one of the most common methods. To split text and numbers you can use a formula based on the FIND function the MIN function and the LEN function with the LEFT or RIGHT function dependin.

These splits can be achieved in Excel 365 assuming names to be split are positioned in cells A4A6 via these two formulas. Use nested SEARCH functions to. If you want to use a formula to split a text string at the first space and then return the left part of the split string this can be done by combining the Left function with the Find function.