Separate Text into Different Cells

4 Flares 4 Flares ×

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

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.

List of names

  1. 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.

Replace contents of destination cells

  1. Select the range containing the data you want to separate.
  2. Click the Data tab on the Ribbon and then Text to Columns.
  3. 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.

Text to Columns step 1

  1. 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.

Text to Columns step 2

  1. Step 3 provides formatting options for the columns. This is not necessary in this example. Click Finish.

Text to Columns step 3

  1. The text is separated. Enter appropriate headings for the columns.

Name separated into different columns

Watch the Video

Learn Excel Formulas Fast

Extract Name from an Email Address

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.

List of email addresses

The following formula was written in cell C3 and copied down to achieve this.

Extract name from email address formula

  • 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 [email protected] 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 [email protected] 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.

Extract Text between Two Characters

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.

Text between two characters in a string

The formula below has been used to achieve this.

Extract text between two characters formula

  • 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

Separate a Number from Text

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.

Extract numbers from text string

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

    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
            LastChar = i - 1
        End If
        IsNumber = True
    End If
    i = i + 1

    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.

5 thoughts on “Separate Text into Different Cells

  1. Hi,
    Nice to see to video and your webpage. I tried your code, it works well.
    If I have a text like ” 99 45.2 87.2369 ” in a cell, how to separate the above string in to three numbers. That is 99, 45.2, 87.2369

    Any help.


  2. Hey, a great piece of code which I have used, however I found a small issue with the last for loop and had to change it to :

    If i = Len(rng) Then
    LastChar = i

    LastChar = i – 1

    it then removed the letters from the end

  3. Great code..
    I have one slight variation. What if the string value is MCPT9-30390 and I need to return 30390.. The code returns 9 which is the number after “T”. How best to modify the code to return 30390 if only one cell in the selection contains that string ?

    • We can use the – as the delimiter. Assuming the value is in cell A1 you could use =MID(A1,FIND(“-“,A1)+2,5). This will work in the example you gave. You may need more if the values are more random.

Leave a Reply

Your email address will not be published. Required fields are marked *