As said, there are so many options for formatting in Excel. For resolving seemingly complicated issues, all we need to do is stop and think a little bit.
In the example below, we will present how to split one cell into multiple cells that have the same format based on the text that this cell consists of.
Split the Text in a Cell into Multiple Cells
For the example, we will split the home addresses that are found in one cell into three or more cells.
These are our addresses currently:
The important thing to define when splitting the text in a cell is to find patterns. In our case, we can see that we have a state name and postal code at the end of the text. We can also see that we have a street name in the middle and the number of the house/apartment at the beginning of the text.
Those are the info that we need to start. First thing first, we will calculate the total number of characters we have in every cell in our range (range A2:A7). To do this, we will input the following formula in cell B2:
|
1 |
=LEN(A2) |
We will drag the formula across our range, and have the following results:
Next thing, you will notice that our state code name and postal code have a total of 8 characters (for example, in cell A2 it would be MT+ blank+ five numbers).
Knowing this data, we can easily extract all the text that precedes the state code name and postal code.
To do this, we will insert this formula in cell C2:
|
1 |
=LEFT(A2,B2-8) |
We will end up with these results:
This formula takes returns the total number of characters – 8 (the number of characters at the end) starting from the left side of the text.
To separate the street number from the street name we will first delete our commas at the end of the text in cells in column C. To do this, we will select the range C2:C7, copy and paste the results in the same range (to lose the formula), and then click CTRL+F. There, we will go to Replace tab and choose to replace “,” with blank:
We will click on Replace All option to lose the commas in our text. We will now separate the street number from the street name.
To do this, we will first go to the D2 cell, and copy the number located in cell C2 (the number 9122).
Then we will select the cells till the end of our range (D2:D7), go to Data >> Data Tools >> Flash Fill:
When we click on it, this is the result that we got:
To extract the street name at this point, we will input the following formula in a cell D2:
|
1 |
=RIGHT(C2,LEN(C2)-LEN(D2)) |
What this formula does is that it takes the text from the right side of cell C2, and extracts the total number of characters in cell C2 minus the characters in column D.
These are the results that we get:
From our original address, we only have the state code and postal code to extract. To do this, we will write down the following formula in cell F2:
|
1 |
=RIGHT(A2,8) |
We will drag the formula, and this is the result that we got:
To separate these two, we first need to copy and paste content in column F (to lose the formulas) and then select the range F2:F7 and go to Data >> Data Tools >> Text to Columns:
Once there, we will click Delimited on the first step:
Then we click Next and choose Space as a Delimiter:
We click Next again and then choose cell G2 as a destination (not to override our existing data):
When we click Finish, these are the results we end up with:
