To use Text to.

How to split text by space/comma/delimiter in excel formula. This method will introduce Kutools for Excels Split Cells utility to split text strings into multiple rows or columns by specified delimiter in Excel. In the opening Split Cells dialog box specify the split type in the Type section check one split separator in the Split by section and click the Ok button. In this accelerated training youll learn how to use formulas to manipulate text work with dates and times lookup values with VLOOKUP and INDEX MATCH count and sum.

Review the result under the Preview section and click the Split button. First is using Excel Formulas to split text and other is Text to Column method. Select the range you will split text strings in our case we select the range A1A9 and click Kutools Text Split Cells.

Click on the Text to Columns icon in the Data Tools group of the Excel ribbon and a wizard will appear to help you set up how the text will be split. Office Tab brings you the tabs in Office Classic Menu brings back the office 2003 menu tools Kutools for excel brings you the powerful Excel tools we bring you the professional Office add-ins. Generic formula for splitting the left text LEFTtext_stringFINDChar text_string-1 Generic formula for splitting the middle text MIDtext_string FINDChar text_string 1 FINDChar text_stringFINDChar text_string1 - FINDChar text_string - 1 Generic formula for splitting the right text.

Select Comma as the delimiter and uncheck any other delimiters. Learn how to split text with a delimiter comma space or special characters using a formula based on the TRIM MID SUBSTITUTE and LEN functions. In our case we check the Split to Columns option and Space option and click the Ok button.

The list is in a column of data and each cell contains the first name AND surname separated by a space. Expand the Split by character group and select one of the predefined delimiters or type any other character in the Custom box. 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.

3 select the Delimited radio option in the first Convert Text to Columns Wizard dialog box and click Next button. Split text strings into multiple rowscolumns by spacecommadelimiter with Kutools for Excel Above method can only split text strings into multiple columns. To split text at specific characters we have two methods.