Hi Rebecca,
You can use a combination of the
LEFT and
RIGHT functions to split the text and numbers into two separate columns.
Here are the steps:
- Insert two new columns next to the column with the text and numbers you want to split.
- In the first new column, use the LEFT function to extract the text from the original column. The formula would be: if the original text is in cell A1. This will extract the first two characters from the cell.
- In the second new column, use the RIGHT function to extract the numbers from the original column. The formula would be: if the original text is in cell A1. This will extract the last two characters from the cell.
- Copy the formulas down to the rest of the rows in the new columns.
Alternatively, if you prefer to insert a delimiter like a comma between the text and number, you can use the
SUBSTITUTE function to replace the characters in the original column with a comma. Here are the steps:
- Insert a new column next to the column with the text and numbers you want to split.
- In the new column, use the SUBSTITUTE function to replace the characters in the original column with a comma. The formula would be:
Formula:
=SUBSTITUTE(A1,LEFT(A1,LEN(A1)-2),"")&","&RIGHT(A1,2)
if the original text is in cell A1. This will replace the first two characters in the cell with nothing, add a comma, and then add the last two characters. - Use "text to columns" to split the new column using the comma as the delimiter.