Extract Email Address from Text

This post will guide you how to extract email address from a text string in Excel. How do I use a formula to extract email address in Excel. How to extract email address from text string with VBA Macro in Excel.

Assuming that you have a list of data in range B1:B5 which contain text string and you want to extract all email addresses from those text string. How to achieve it. You can use a formula or VBA Macro to achieve the result. Let’s see the below introduction.

1. Extract Email Address from Text with a Formula

To extract email address from text string in cells, you can use a formula based on the TRIM function, the RIGHT function, the SUBSTITUTE function, the LEFT function, the FIND function, the REPT function and the LEN function. Just like this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(" ",B1&" ",FIND("@",B1))-1)," ",REPT(" ",LEN(B1))),LEN(B1)))

Select the adjacent Cell C1, and type this formula, and press Enter key in your keyboard, and then drag the AutoFill Handle over other cells to apply this formula.

exctract email address from text1

2. Extract Email Address from Text with User Defined Function

You can also write a User Defined Function with VBA Code to extract email address quickly, just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Function ExtractEmailFromText(s As String) As String
    Dim AtTheRateSignSymbol As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"
    
    AtTheRateSignSymbol = InStr(s, "@")
    If AtTheRateSignSymbol = 0 Then
        ExtractEmailFromText = ""
    Else
        TempStr = ""
        For i = AtTheRateSignSymbol - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        
        If TempStr = "" Then Exit Function
        
        TempStr = TempStr & "@"
        
        For i = AtTheRateSignSymbol + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    
    If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1)
    
    ExtractEmailFromText = TempStr
End Function

Step5: Type the following formula into blank cells and then press Enter key.

=ExtractEmailFromText(B1)

Type this formula into a blank cell and then press Enter key in your keyboard.

Step6: lets see the result:

exctract email address from text3

3. Video: Extract Email Address from Text in Excel

This video will demonstrate a step-by-step instruction on how to use the formula and VBA code to extract email addresses from a block of text in Excel, making it easy to manage and organize your contact information.

How to Reverse Concatenate Formula in Excel

If you have ever needed to split text that was combined using the concatenate function, and you may be want to know if there is a way to reverse the process and spearate the text into its orginal form.

This post will guide you how to reverse the concatenate function using a formula in Excel, as well as “Text to Columns” feature. How do I split the text string into the muliptle cells with a formula in Excel. How to split text data of a cell to multiple cells in Excel.

1. Video: How to Reverse Concatenate Formula in Excel

This video tutorial demonstrates the steps for using a formula and the “Text to Columns” feature to split text in Excel.

2. Reverse Concatenate Formula

To reverse concatenate formula in Excel, you can use a formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, and the COLUMNS function.
Assuming that you have a list of data in range B1:B4 that contain text string, and you want to split the string by comma character. you can write down the following formula:

=TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

Type this formula into a blank cell and then press Enter key and then drag the Fill Handle down to other cells to apply this formula.

reverse concatenate function1

You will see that all text string in range of cells B1:B4 have been split into multiple cells by a comma character.

This method can be useful for working with data that has been combined using the concatenate function or for importing data from other sources that may be in a combined format.

3. Split text string to Multiple Cells with Text To Column Feature

The opposite of concatenate in excel is to split or separate text that have been combined using the concatenate function. If you want to split text into separate cells in excel, you can also use the Text To column Feature to reverse the concatenate formula to split text string in the range of cells B1:B4 to multiple cells in Excel. Just do the following steps:
Step1: select the range of cells that you want to reverse.

reverse concatenate function2
Step2: go to DATA tab, click Text To column command under Data Tools group. and the Convert  Text to Columns Wizard dialog will open.

reverse concatenate function3
Step3: select Delimited option in the Original data type section, then click Next  button.

reverse concatenate function4
Step4: check Other checkbox, and type the delimiter that you want to use, such as: a comma character. and then click Next button.

reverse concatenate function5
Step5: select General option under column data format section, and select a destination cell where you want to place the split results. 

reverse concatenate function6 

Step6: Once you are satisfied with the preview, click on “Finish” to split the text into separate columns.

reverse concatenate function7

Your text will now be split into separate columns based on the delimiter or width you specified.

4. Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT  (text, number_times)…
  • Excel COLUMNS function
    The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….

Repeating Character n Times in Excel

In daily life, we can use repeated characters to indicate the magnitude or priority of something. For example, a single “!” indicates a minor case, double “!!” indicates a medium case, and triple “!!!” indicates a high case, and so on. In an Excel worksheet, we can use REPT function to output a different number of repeated characters based on the input value to mark the difference. This approach is more convenient than creating a chart in Excel worksheet.

In today’s example, we want to indicate different incomes by outputting different numbers of “A” characters. Assuming the rule is one A per $1000, for example, if income is $1000, record an “A” in the “Level” column, and if income is 1750, just near 2000, but not enough for 2000, still an “A”.

Look at the following example. We use different amounts of repeated characters “A” to indicate different income levels.

Repeating Character n Times in Excel1

In this example, you can also replace the character “A” with other characters, or even a string.

FORMULA

Excel not only contains many very useful mathematical functions, but also contains several uncommon but useful functions. REPT function is one of these uncommon functions.

Excel REPT function can output repeating characters according to the input times. It is very helpful for us to build a simple comparison with normal or special characters.

Syntax:

= REPT(text, number_times)

Example:

REPT(“*”,5) – Outputs *****

 In this example, the text is the letter “A”, character “A” should be enclosed into double quotes, and the number_times is A2/1000, depending on how many 1000s the income value contains.

The formula is:

= REPT(“A”, A2/1000)

EXPLANATION

REPT is a very simple function in Excel. We only need to confirm the output character and the number of times to output proper characters.

1.number_times Is Not an Integer & <1

In this example, the number of outputs is determined by the mathematical expression A2/1000.

A2/1000 -> Calculate repeating times

Calculate A2/1000 in Excel formula bar. The result is 0.7.

Repeating Character n Times in Excel1

The REPT function now has two parameters confirmed, the output character is “A” and the times is “0.7”. Since times is not an integer and is less than 1, it will be truncated, so the parameter is equal to 0, no “A” is output, and an empty cell is displayed in B2.

Repeating Character n Times in Excel1

2.number_times Is Not an Integer & >1

In B4, the formula is:

=REPT("A",A4/1000).

A4/1000=1.5 –> Repeating times 1.5 is not an integer, decimal part is truncated.

Repeating Character n Times in Excel1

1.5 is rounded down to the nearest integer 1. So only one “A” is output and displayed in B4.

Repeating Character n Times in Excel1

3.number_times Is an Integer

In B3, the formula is =REPT(“A”,A3/1000).

A3/1000=1 –> Repeating times is an integer

So REPT function only returns one “A”.

Repeating Character n Times in Excel1

EXPAND

Wingdings character set and equivalent Unicode characters (alanwood.net)

Sometimes we may want to display some special characters or icons in an Excel worksheet. We can still apply the REPT function to output characters, but these special characters cannot be entered into Excel formulas because they are not accepted. At this point we need to get help from CHAR function in Excel.

CHAR function can return a character based on the entered number.

Syntax: CHAR(Number)

Depending on the font, CHAR will return different characters even if the same number is entered.

Repeating Character n Times in Excel1

Pick font Calibri.

Repeating Character n Times in Excel1

Pick font Wingdings.

Repeating Character n Times in Excel1

To output the smileface instead of letter “A” in the above example, we simply enter char(74) in the REPT “text” argument to replace letter “A”.

Related Functions

  • Excel CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The REPT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the REPT function is as below:= REPT  (text, number_times)….

Extract Multiple Lines From A Cell

Suppose that you have listed some text in a single cell which is separated by the line break(you can do it by pressing ALT + ENTER after entering the text), and now you want to extract multiple lines of text from a single cell into a separate cell, just like it has been done in the screenshot below:

You would now think that you can do it manually, but keep it into your consideration that it seems easy when there are one or two cells from which you want to extract multiple lines of text into the separate cells, but when it comes to multiple cells from which you need to extract multiple lines of text into the separate cells then doing it manually would be the foolish attempt because by this you would get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting multiple lines of text into separate cells would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

General Formula:


The Following formula would help you out for extracting multiple lines of text into seprate cells:

=TRIM(MID(SUBSTITUTE(B1,CHAR(10),REPT(” “,LEN(B1))), (N-1)*LEN(B1)+1, LEN(B1)))

or

=TRIM(MID(SUBSTITUTE($B1,CHAR(10),REPT(" ",LEN($B1))), (C$1-1)*LEN($B1)+1, LEN($B1)))

extract multile lines from one cell1

Syntax Explanations:


Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to extracting the multiple lines of text into the separate cells:

  • TRIM: This function contributes to removing the extra spaces from the text, whether the space would be at the start or at the end of the text string.
  • REPT: In MS Excel, the REPT function is used to repeat characters to a given number of times.
  • MID: The MID function contributes to extracting the number (beginning from the left side) or characters from the given string.
  • FIND: In MS Excel, the MID function contributes to finding out the specific text string inside the other.
  • LEN: In MS Excel, the LEN functioncontributes to finding out the length of the text string.
  • SUBSTITUTE: This function replaces the existing text with new text in a text string when you want to replace the text based on its content, not on its position.
  • B1: It contributes to representing the input value.
  • Comma symbol (,): This comma symbol acts as a separator that separates the list of values.
  • Parenthesis (): The primary purpose of this parenthesis symbol is to group the various elements.
  • Minus Operator (-): This minus symbol contributes to subtracting any two values.
  • Plus operator (+): This plus symbol adds the values.

Let’s See How This Formula Works:


The main point is that using the SUBSTITUTE and REPT functions, this formula searches for a line delimiter (“delim”) and then replaces it with many spaces.

Note that if you are using Excel on Mac and if your Excel version is old, then instead of using “CHAR(10)”, you should use “CHAR(13)” as for returning a character based upon its numeric code CHAR function is used.

The overall length of the text in the cell determines the number of spaces needed to replace the line delimiter. The formula then uses the MID function to extract the desired line. The following snippet of the formula carries out the starting point

(N-1)*LEN(B1)+1

In the above snippet, the “N” stands for “nth line,” which is picked up with the reference from the 1th row. The snippet “ LEN(B1) “ is used to identify the total number of characters extracted and is definitely equal to the length of the overall text string.

Now, to trim out all the extra space characters and to return just the line text, the TRIM function is used.

Built-In Text to Columns Feature:


Moreover, you should know about it that there is also a built-in feature in MS Excel named Text to Columns feature (achieved by pressing Control + J ) that splits up the text according to the delimiter of your choice as if you are thinking to use this built-in way for extract multiple lines of text from a single cell into the separate cell instead of the formula explained above then stop here, as this built-in feature is not appreciated over the formula by the professionals because with the use of this built-in feature results are not more concise or desirable than the results after using the formula.

Related Functions


  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words. The syntax of the TRIM function is as below:=TRIM(text)…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The REPT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the REPT function is as below:= REPT  (text, number_times)….
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…

How to Count Number of Cells that Contain Exactly N Characters in Excel

This post will guide you how to count the number of cells that contain exactly N charcters within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through COUNTIF function. But if there is an easy way to count cells that contain a certain length  in your data range.

Count Number of Cells that Contain Exactly N Characters


If you want to count the number of cells that contain a certain length or a certain number of characters(7 characters) in range A1:B6, you can create a formula based on the COUNTIF function. Like this:

=COUNTIF(A1:B6,”???????”)

Or

=COUNTIF(A1:B6,REPT(“?”,7))

count number of cells that contain certain characters1

count number of cells that contain certain characters2

You would see that the above formulas will count the number of cells in a given range A1:B6 which contain exactly 7 characters.

Note: A1:B6 is the range from which to count the cells that contain seven characters.

LET’S SEE THAT HOW THIS FORMULA WORKS:

This above formula will use the question mark “?” character to match one character. So if you wish to match seven characters, and you just need to add 7 characters into the formula.

The REPT function will return a text string made of exactly 7 question marks.

The COUNTIF function will count all the cells in the range that contain exactly 7 characters.

 Related Functions


  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT  (text, number_times)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

 

How to extract nth word from text string

This post will guide you how to extract the nth word from a text string in excel. How do I get the nth word from text string in a cell using excel formula. How to write a User Defined Function to extract nth word from text.

Extract nth word

If you want to extract the nth word from a text string in a single cell, you can create an excel formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function and the LEN function.

You can use the SUBSTITUTE function replaces each space character with a number of space characters returned by the REPT function. The number of space characters should be equal to the overall length of the original text in cell. So you also need to use the LEN function to combine with the REPT function to create those spaces. Then you can use the MID function to extract the nth word and the starting position can get from the formula (N-1)*LEN(B1)+1 , and the num_chars should be equal to the length of the original text.

Assuming that you want to extract the third word from text string in Cell B1, you can write down the following excel formula:

=TRIM(MID(SUBSTITUTE(B1," ", REPT(" ",LEN(B1))), (3-1)*LEN(B1) +1, LEN(B1)))

Let’s see how this formula works:

=REPT(” “,LEN(B1))

This formula will return a new text string that contain a number of the space characters and the number of space character is equal to the length of the original text string in Cell B1. Then it goes into the SUBSTITUTE function as its new_text argument.

 

=SUBSTITUTE(B1,” “, REPT(” “,LEN(B1)))

extract nth word1

This formula replace each space character with new_text that returned by the REPT function. Then it goes into the MID function as its Text argument.

 

=(3-1)*LEN(B1) +1

extract nth word2

This formula returns the starting position of the third word from the text in Cell B1. Then it passed into the MID function as its start_num argument.

 

=MID(SUBSTITUTE(B1,” “, REPT(” “,LEN(B1))), (3-1)*LEN(B1) +1, LEN(B1))

extract nth word3

The both Text and start_num arguments are returned by the above two formula, so this formula extracts the third word from the text in Cell B1.

 

=TRIM()

extract nth word4

The TRIM function removes all extra space characters and just leave only one between words.

Extract Nth word with User Defined Function

You can also write a User Defined Function to extract the nth word from the text string in a cell in Excel VBA:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

Function GetNthWord(Source As String, Position As Integer)
    Dim arr() As String
    arr = VBA.Split(Source, " ")
    xCount = UBound(arr)
    If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
        GetNthWord = ""
   Else
        GetNthWord = arr(Position - 1)
    End If
End Function

extract nth word5

5# back to the current worksheet, then enter the below formula in Cell C1:

extract nth word6


Related Formulas

  • count specific words in a cell or a range
    If you want to count the number of a specific word in a single cell, you need to use the SUBSTITUTE function to remove all that certain word in text string, then using LEN function to calculate the length of the substring that without that specific word.…
  • Get first word from text string
    If you want to extract the first word from a text string in a cell, you can use a combination of the IF function, the ISERR function, the LEFT function and the FIND function to create a complex excel formula..…
  • Get last word from text string
    If you want to get the last word from a text string, you can create an excel formula based on the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function..…
  • Extract word that starting with a specific character
    Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT  (text, number_times)…
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…

How to extract word that containing a specific character

This post will guide you how to extract word that containing a specific character using formula in excel. How to get a word that contains a specific character (such as: @, comma, hash, etc.) in a text string in one cell.

Extract word that containing a specific character

If you want to extract word that contains a hash character in a text string in Cell B1, you can use a combination of the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the FIND function to create an excel formula as follows:

=TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",255)),FIND("#",SUBSTITUTE(B1," ",REPT(" ",255)))-100,255))

Let’s see how this formula works:

= SUBSTITUTE(B1,” “,REPT(” “,255))

extract word contain specific character1

The REPT function will return a new text string that contains 255 empty spaces.  And it goes into the SUBSTITUTE function as its new_text argument.  Then the SUBSTITUTE function will replace all empty string with new text value returned by the REPT function.

 

= FIND(“#”,SUBSTITUTE(B1,” “,REPT(” “,255)))-100

extract word contain specific character2

This formula will locate the position of the first hash character (#) in a text string that returned by the SUBSTITUTE function.

 

= MID(SUBSTITUTE(B1,” “,REPT(” “,255)),FIND(“#”,SUBSTITUTE(B1,” “,REPT(” “,255)))-100,255)

extract word contain specific character3

This MID function will extract 255 characters from a text string (returned by the SUBSTITUTE function) at a specific position that returned by the FIND function. Then the word that contain hash character will be returned.

 

=TRIM()

extract word contain specific character4

The TRIM function removes all spaces from text string returned by the MID function, just leave one space between words.


Related Formulas

  • Split Multiple Lines from a Cell into Rows
    If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
  • Extract word that starting with a specific character
    Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT  (text, number_times)…

How to extract word that starting with a specific character

This post will guide you how to extract a word that starts with a specific character in excel.

Extract word that starting with a specific character

For example, Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula as follows:

=TRIM(LEFT(SUBSTITUTE(MID(B1, FIND("@",B1), LEN(B1))," ",REPT(" ",LEN(B1))),LEN(B1)))

Let’s see how this formula works:

=LEN(B1)

The LEN function returns the number of characters in a text string in Cell B1. The returned result goes into the MID function as its num_chars argument.

 

= FIND(“@”,B1)

extracts word begins specific character1

The FIND function returns the position of the first specific character “@” sign in Cell B1. It returns 9. It goes into the MID function as its start_num argument.

 

=MID(B1, FIND(“@”,B1), LEN(B1))

extracts word begins specific character2

The MID function extracts a substring at the starting position (returned by the FIND function) and num_chars value (returned by LEN function)

 

=REPT(” “,LEN(B1))

This formula repeats empty string a specified number of times returned by the LEN function. It will go into the SUBSTITUTE function as its new_text argument.

 

= SUBSTITUTE(MID(B1, FIND(“@”,B1), LEN(B1)),” “,REPT(” “,LEN(B1)))

extracts word begins specific character3

The SUBSTITUTE function will replace all empty string with another new text string returned by the REPT function from a text string returned by the MID function.  Then the returned value goes into the LEFT function as its Text argument.

 

= LEFT(SUBSTITUTE(MID(B1, FIND(“@”,B1), LEN(B1)),” “,REPT(” “,LEN(B1))),LEN(B1))

extracts word begins specific character3

The LEFT function extracts a specified number of the characters from a text string returned by the SUBSTITUTE function, starting from the leftmost character and the num_chars value is equal to the length of the string in Cell B1.

=TRIM()

extracts word begins specific character5

The TRIM function removes all spaces from text string returned by the LEFT function, just leave one space between words.


Related Formulas

  • Split Multiple Lines from a Cell into Rows
    If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
  • Extract word that containing a specific character
    If you want to extract word that contains a hash character in a text string in Cell B1, you can use a combination of the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the FIND function to create an excel formula.…

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])….
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT  (text, number_times)…

How to Extract Text between Commas

This post will guide you how to extract text between commas in excel.

Before we talked that how to extract text between brackets using a formula with MID and SEARCH functions. And this post will extract text between commas instead of brackets. The biggest difference is that the brackets have the left and right, but the comma is not. So you can’t use the similar formula for commas.

To extract text between commas in Cell B1, you can use the following formula based on the SUBSTITUTE function, the MID function and the REPT function.

=SUBSTITUTE(MID(SUBSTITUTE("," & B1&REPT(" ",6), "," , REPT(",",255)),2*255,255), ",", " ")

Let’s see how this formula works:

=”,” & B1&REPT(” “,6)

extract text between commas 1

The REPT function will repeat empty string 6 times.

The concatenation operator (&) will join together one comma and text string in Cell B1 and another empty text string returned by REPT function to build a new text string. It will go into SUBSTITUTE function as its Text argument. This formula returns as a new text string like this:

,excel,word,ppt

 

= SUBSTITUTE(“,” & B1&REPT(” “,6), “,” , REPT(“,”,255))

The second argument of SUBSTITUTE function is set to a comma character as its Old_text argument.

The new_text value will use the result returned by the REPT function, it will repeat a comma character 255 times.

So this formula will replace all commas with new_text 255 commas in new text string.  The returned string is like this:

,,,,,,,,,,,,,,,,,excel,,,,,,,,,,,,,,,,,word,,,,,,,,,,,,,,,ppt        (255 commas between each word)

 

= MID(SUBSTITUTE(“,” & B1&REPT(” “,6), “,” , REPT(“,”,255)),2*255,255)

extract text between commas 3

This MID function will extract 255 characters from a text string (returned by the above SUBSTITUTE function) at a specific position 2*255.  So it returns like this:

,,,,,,word,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

 

=SUBSTITUTE(MID(SUBSTITUTE(“,” & B1&REPT(” “,6), “,” , REPT(“,”,255)),2*255,255),”,”,””)

extract text between commas 4

This formula will use a text string returned by the MID function as its Text argument and then replace all commas with the empty text string. So the last returned result is text that you want to extract.


Related Formulas

  • Extract Text between Parentheses
    If you want to extract text between parentheses in a cell, then you can use the search function within the MID function to create a new excel formula…
  • Extract Text between Brackets
    If you want to extract text between brackets in a cell, you need to create a formula based on the SEARCH function and the MID function….
  • Check If Cell Contains All Values from Range
    If you want to check if a cell contains all values in a list, you can use a combination of the SEARCH function, the SUMPRODUCT function, the ISNUMBER function and COUNTA function…

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The REPT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the REPT function is as below:= REPT  (text, number_times)….

How to Split Multiple Lines from a Cell into Rows

This post will guide you how to split multiple lines from a cell into separated rows or columns in Excel. You will learn that how to extract text string separated by line break character into rows in excel 2013.

In the previous post, we talked that how to split text string by only one line break character. And this post will talk that how to split text string by multiple line break character (multiple lines) into rows.

Split Multiple Lines from a Cell into Rows

If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula.

For example, to extract multiple lines in Cell B2, then put it into C2, D2 or E2. You can write down the following excel formula:

=TRIM( MID(SUBSTITUTE( $B2, CHAR(10), REPT( " ",LEN($B2) ) ), (C$1-1)*LEN($B2)+1, LEN($B2)) )

Let’s see how this formula works:

=CHAR(10)

The CHAR function returns the character specified by a number.  If the number is 10, it will return a line break character in text string.

 

=LEN($B2)

The LEN function returns the total length of text string in Cell B2.

 

= REPT( ” “,LEN($B2)

This formula will repeat empty string a given number of times that returned by the LEN function.  It will return a new text string.

 

=SUBSTITUTE( $B2, CHAR(10), REPT( ” “,LEN($B2) ) )

split multiple line from a cell1

The SUBSTITUTE function will replace all line break characters with a new text string returned by the REPT function in a text string in Cell B1. The returned string will go into the MID FUNCTION as its text argument.

 

= (C$1-1)*LEN($B2)+1

This formula returns the starting position of the first, second, or third or nth substring. The returned value goes into the MID function as its start_num argument.

 

=MID(SUBSTITUTE( $B2, CHAR(10), REPT( ” “,LEN($B2) ) ), (C$1-1)*LEN($B2)+1, LEN($B2))

split multiple line from a cell2

The returned result of the last LEN function is the number of the characters that you want to extract from a text string in Cell B2. It goes into the MID function as its num_chars argument.

The MID function extract a substring from a text string returned by the SUBSTITUTE function based on start_num and num_chars arguments. The returned substring is what you want to extract.

=TRIM()

split multiple line from a cell3

The TRIM function removes all spaces from text string except for single spaces between words.


Related Formulas

  • Split Text String by Specified Character in Excel
    you should use the Left function in combination with FIND function to split the text string in excel. And we can refer to the below formula that will find the position of “-“in Cell B1 and extract all the characters to the left of the dash character “-“.=LEFT(B1,FIND(“-“,B1,1)-1).…
  • Split Text String to an Array
    If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string…
  • Split Text String by Line Break in Excel
    When you want to split text string by line break in excel, you should use a combination of the LEFT, RIGHT, CHAR, LEN and FIND functions. The CHAR (10) function will return the line break character, the FIND function will use the returned value of the CHAR function as the first argument to locate the position of the line break character within the cell B1.…
  • Split Text and Numbers
    If you want to split text and numbers, you can run the following excel formula that use the MIN function, FIND function and the LEN function within the LEFT function in excel. And if you want to extract only numbers within string, then you also need to RIGHT function to combine with the above functions..…

Related Functions

  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words. The syntax of the TRIM function is as below:=TRIM(text)…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The REPT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the REPT function is as below:= REPT  (text, number_times)….
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…

 

Excel Rept Function

This post will guide you how to use Excel REPT function with syntax and examples in Microsoft excel.

Description

The Excel REPT function repeats a text string a specified number of times.

The REPT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.

The REPT function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the REPT function is as below:

= REPT  (text, number_times)

Where the REPT function arguments are:
Text -This is a required argument. The text string that you want to repeat.
Number_times – This is an required argument.  The number of times that you want to repeat
Note: If number_times is zero, the REPT function will return empty text. And If number_times value is not an integer, it is truncated to an integer value.

Example

The below examples will show you how to use Excel REPT Text function to repeat a text string a given number of times.

#1 To repeat a text string in B1 cell 3 times, just using the following formula:= REPT (B1,3).

excel text function rept example1