To split text at an arbitrary delimiter comma space pipe etc you can use a formula based on the TRIM MID SUBSTITUTE REPT and LEN functions.

Formula to split text in excel by space. This formula involves nesting SEARCH to find the second instance of space from the left. The formulas used to do this are in two separate cells. 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.

Text to Columns feature for splitting text When splitting text in Excel the Text to Columns is one of the most common methods. These splits can be achieved in Excel 365 assuming names to be split are positioned in cells A4A6 via these two formulas. 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.

It can also be a valid cell reference within a workbook. The first name starts with the first character from the left and ends at the ninth character the second space. DOWNLOAD SPACE FORMULA FILE HERE Download COLUMN B Formula.

How to count a specific character in a string in Excel Finally the TRIM function removes the cushion space characters around the separated data. Supposing the cells you want to split look similar to this. To split a text string at a space or comma we can use the FIND LEFT MID and RIGHT functions.

In this case you need to apply two formulas in order to get the text before the first space and another part of text after the first space. This feature can split text by a specific character count or delimiter. The only difference is that you will need the CHAR function to supply the line break character since you cannot type it directly in the formula.

Generic formula for getting all before the first space LEFTA1FIND A1-1 Generic formula for getting all after the first space. SEQUENCE 1 column number-1LEN text1 If you want to calculate the column count dynamically you can count the delimiter character in the rows and use that number in the formula. Well deal with this later.