Splitting Text across Columns in Excel

The video guide shows a method for splitting strings of text into multiple columns by using characters such as commas. There is a slight issue with the technique used because the addresses output into the second column have leading spaces which may be invisible to other users of the sheet. Excel's Text to Columns feature works best with data imported directly from a database where the source data came from columns originally.

Running the TRIM function on a column of data will remove any leading or trailing spaces. In the example, you could put the formula '=TRIM(B1)' in cell C1, copy it down column C, before copying and pasting special the entire column as values. You would then be entitled to delete column B.

An alternative method would be Excel's Flash Fill option which is adjacent to Text to Columns in the Data ribbon. If you entered 'Simon Williams' in B2 and '5 Memorial Dr.' in C2, you could complete the rest of the table by clicking Flash Fill with the cursor in B3. Excel would understand the pattern of removing a comma and a space before the number. This method would not work if the names varied in the number of words and the addresses did not always start with numbers. If splitting the data requires some sort of visual inspection and understanding of the contents then the best option may be to upload the file to your preferred AI assistant and get it to solve the problem.

If you are planning to add more data in the future then it is better to solve the problem using formulas as these can be extended as required. You could use the FIND function to look for a comma followed by a space, and identify the portion of the string required in each column using the LEFT and RIGHT functions. An AI assistant would also be able to write the formula.