A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.
This article looks at four techniques for separating text. Use the links below to jump to a specific technique.
- Using Text to Columns to separate names
- Extract name from an email address
- Extract text between two characters
- Separating a number from text
Excel has a built-in feature for separating text into different cells and it is known as Text to Columns. It is very good and easy to use.
Let’s have a look at it being used to separate a name into different cells.
- You should first ensure that you have an empty column to the right of the range containing the data for separation (like in the image above). If not, the Text to Columns feature will warn you that it will overwrite the contents of this column.
- Select the range containing the data you want to separate.
- Click the Data tab on the Ribbon and then Text to Columns.
- The Convert Text to Columns Wizard appears and asks how to separate the text. The feature automatically detects that the data is delimited. If not, the fixed width option enables you to specify where the separation should occur.
- Step 2 asks what delimiter character is being used to separate the text. In this example the delimiter is a space. Check the space box and ensure others are unchecked.
- Step 3 provides formatting options for the columns. This is not necessary in this example. Click Finish.
- The text is separated. Enter appropriate headings for the columns.
Another common requirement is the need to extract a name from an email address. The image below shows names in column C that have been extracted from the email addresses in column B.
The following formula was written in cell C3 and copied down to achieve this.
- The LEFT function is used to extract text from the beginning of another piece of text. It has been used in this formula to extract al the text to the left of the ‘@’ symbol.
- The FIND function is used to return the position of one piece of text inside another. In this formula it has been used to find the ‘@’ symbol for the LEFT function.
- The SUBSTITUTE function is then used to replace the ‘.’ In the email address with a space.
Watch the Video
Do you store email addresses in your spreadsheets. Check out this article on validating email addresses in Excel.
To extract text between two characters will be more complex. In the example below, the text in column C has been extracted from between the two ‘/’ in column B.
The formula below has been used to achieve this.
- The LEFT function has been used to extract text from the beginning of a piece of text.
- In the first part of the LEFT function, a MID function is used to return all the text after the first ‘/’. FIND has been used to locate the ‘/’ and LEN used to return all the remaining characters.
- In the second part of the LEFT function, the same MID function has been used to return everything after the first ‘/’. This time though it is combined with another FIND function to get the position of the other ‘/’ in the extracted text. 1 is then subtracted to return the number of characters LEFT needs to extract.
Not a straightforward formula, but hopefully that makes some sense.
Watch the Video
Separating a number from text will be more difficult. It can be done using a formula but it would require a chunky array formula. Therefore I opted for a VBA solution.
The image below shows the numbers in column C that have been extracted from the text in column B using this macro.
The VBA code below can be used to separate a number from text in a cell. Comments have been added to the code for explanation.
Sub ExtractNumber() Dim i As Integer 'To count throught the characters in a cell Dim rng As Range 'Range object to loop through all the cells in the selected range Dim TestChar As String 'The characte being tested Dim IsNumber As Boolean 'Used as a way of exiting the loop when a number, or text has been found Dim StartChar As Integer 'Stores the position of the first number in the text Dim LastChar As Integer 'Stores the position of the last number in the text Dim NumChars As Integer 'Stores how many numbers there are for extraction 'Loop for each cell in user selection For Each rng In Selection IsNumber = False i = 1 'Loop to identify position of first number in the text Do While IsNumber = False And i <= Len(rng) TestChar = Mid(rng, i, 1) If IsNumeric(TestChar) = True Then StartChar = i IsNumber = True End If i = i + 1 Loop IsNumber = False 'Loop to identify the last number in the text Do While IsNumber = False And i <= Len(rng) TestChar = Mid(rng, i, 1) If IsNumeric(TestChar) = False Or i = Len(rng) Then If i = Len(rng) Then LastChar = i Else LastChar = i - 1 End If IsNumber = True End If i = i + 1 Loop NumChars = LastChar - StartChar + 1 rng.Offset(0, 1).Value = Mid(rng, StartChar, NumChars) Next rng End Sub
Subscribe to the Excel VBA course to learn more about these techniques and much more.