Tag: Excel LEN

How to Split Cells by the First Space in Texts in Excel

Sometimes we may use space to separate texts to different groups in one cell, if we want to split this cell to multiple columns refer to space, we can implement this via ‘Text to Columns’ feature. But is there any way to split one cell to only two cells by the first space in the texts? In this free tutorial, we will share you some useful functions in excel to split cells per your demand.

Precondition:

In product list we use space to separate product properties.

How to Split Cells by the First Space in Texts in Excel1

Now we want split it to two parts in two different columns by the first space. See expectation below:

How to Split Cells by the First Space in Texts in Excel2

1. Split Cells to Two Columns by the First Space in Texts by Formula

Step1: In B2 enter the formula:

=LEFT(A2,FIND(" ",A2)-1)
How to Split Cells by the First Space in Texts in Excel3

Step2: Click Enter to get result. Verify that text before the first space is extracted and saved in B2 properly.

How to Split Cells by the First Space in Texts in Excel4

Step3: Drag the fill handle down till the end of the list. Verify that texts from the first space are displayed properly.

How to Split Cells by the First Space in Texts in Excel5

Step4: In C2 enter the below formula to extract the left texts after the first space.

=RIGHT(A2,LEN(A2)-FIND(" ",A2))
How to Split Cells by the First Space in Texts in Excel6

Step5: Click Enter to get result. Verify that texts are displayed properly.

How to Split Cells by the First Space in Texts in Excel7

Step6: Drag the fill handle down till the end of the list. Now texts are split to two columns by the first space properly.

How to Split Cells by the First Space in Texts in Excel8

2. Split Cells to Two Columns by the First Space in Texts with VBA Code

You can use a VBA Code that prompts the user to select a range of cells and a destination cell, and then splits the cells in the selected range to two columns based on the first space in the text.

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, go to “Insert” on the top menu and select “Module“.

Step3: Paste the below VBA code into the new module.

Split Cells to Two Columns by the First Space in Texts vba 1.png
Sub SplitCellsByFirstSpace_ExcelHow()
    ' Prompt the user to select a range of cells
    Dim selectedRange As Range
    Set selectedRange = Application.InputBox("Select a range of cells:", Type:=8)
    
    ' Prompt the user to select a destination cell
    Dim destinationCell As Range
    Set destinationCell = Application.InputBox("Select a destination cell:", Type:=8)
    
    ' Insert the formula to split the text at the first space
    For Each cell In selectedRange
        cell.Offset(0, 2).Formula = "=RIGHT(" & cell.Address(False, False) & ",LEN(" & cell.Address(False, False) & ")-FIND("" "", " & cell.Address(False, False) & ",1))"
        cell.Offset(0, 1).Formula = "=LEFT(" & cell.Address(False, False) & ",FIND("" "", " & cell.Address(False, False) & ",1)-1)"
    Next cell
    
    ' Copy the formula to the destination cell and adjacent cell
    selectedRange.Offset(0, 1).Resize(selectedRange.Rows.Count, 2).Copy destinationCell
    
    ' Convert the formula to values in the destination cells
    destinationCell.Resize(selectedRange.Rows.Count, 2).Value = destinationCell.Resize(selectedRange.Rows.Count, 2).Value
End Sub

Step4: Press F5 to run the macro or click the “Run” button on the toolbar.

Step5: A dialog box will appear asking you to select a range of cells. Click and drag your mouse to select the range of cells you want to split.

Split Cells to Two Columns by the First Space in Texts vba 2.png

Step6: Another dialog box will appear asking you to select a destination cell. Click on the cell where you want to place the split data.

Split Cells to Two Columns by the First Space in Texts vba 3.png

Step7: The macro will split the data in the selected range into two columns based on the first space in the text, and place the split data into the destination cell and adjacent cells.

Split Cells to Two Columns by the First Space in Texts vba 4.png

3. Video: Split Cells to Two Columns by the First Space in Texts

This video will demonstrate how to split cells to two columns by the first space in texts using Excel formulas and VBA code.

4. Related Functions

  • 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 LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • 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 RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[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 Count Spaces before the Text String

This post will guide you how to count the number of spaces before the text string in a cell in Excel. How to count the leading spaces before the text string in one cell with formula in excel. How do I create an excel formula that counts the number of spaces before the text string.

1. Count number of spaces before a string using a Formula

Assuming that you have lots of cells contain text string with different levels of indentation on them. You want to count the number of spaces before each test string in cells, and then you want to know how to count the number of spaces before the text string in each cell.

You can create an excel formula based on the FIND function, the LEFT function, and the TRIM function.

For example, let’s count the number of spaces before text string in Cell B1, you can write down the following excel formula:

=FIND(LEFT(TRIM(B1),1),B1)-1
count spaces before text1

Let’s see how this formula works:

The TRIM function can be used to remove all spaces at the start or end of the text string, then you can get a text string without any spaces. The returned result will pass into the LEFT function to get the leftmost characters of the result without spaces.

Then use the FIND function to search for that characters in the original string, and it returns the position of the first character of the searching characters in the original string. The result is subtracted 1 to get the number of spaces before the text string in Cell B1.

When you use this formula, you do not need to care how many other spaces there are or where they are.

If there are no spaces at the end of the text string in each cell, then you also can use the following formula:

=LEN(B1)- LEN(TRIM(B1))
count spaces before text2

2. Count Spaces before the Text String using User Defined Function with VBA Code

You can create a user-defined function in Excel using VBA (Visual Basic for Applications) to count the number of spaces before a text string. Just do the following steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: Click Insert > Module to insert a new module.

Step3: Type the following code into the module:

How to Count Spaces before the Text String vba 1.png
Function CountSpaces_Excelhow(str As String) As Integer
    Dim i As Integer
    For i = 1 To Len(str)
        If Mid(str, i, 1) <> " " Then
            Exit For
        End If
    Next i
    CountSpaces_Excelhow = i - 1
End Function

Step4: Enter the formula into a cell, then press Enter key to apply it.

=CountSpaces_Excelhow(B1)
How to Count Spaces before the Text String vba 2.png

3. Video: Count Spaces before the Text String

This video will show you how to count spaces before the text string in Excel using a formula or VBA code.

4. Related Formulas

  • 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 nth word from text string
    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..…
  • 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.…

5. Related Functions

  • 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 LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • 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 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)….

How to Remove the First/Last Word from Text string in Cell

This post will guide you how to remove the first and the last word from a text string in cells using a formula or User defined function with VBA code in Excel 2013/2016/2019/365. How do I use a formula to remove first and last word of a text string in Excel.

1. Remove the First Word from Text String using Formula

If you want to remove the first word from a text string in cells in Excel, you can use a formula based on the RIGHT function, the LEN function and the FIND function. Like this:

=RIGHT(B1,LEN(B1)-FIND(" ",B1))

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

remove first last word in cell1

2. Remove the Last Word from Text String using Formula

If you want to remove the last word from a text string, you can use a formula based on the LEFT function, the TRIM function, the FIND function, and the SUBSTITUTE function. Like this:

=LEFT(TRIM(B1),FIND("~",SUBSTITUTE(B1," ","~",LEN(TRIM(B1))-LEN(SUBSTITUTE(TRIM(B1)," ",""))))-1)

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

remove first last word in cell2

3. Remove the First Word from Text String using User Defined Function with VBA Code

You can create a User Defined Function in VBA to remove the first word from a text string in Excel by following these steps:

Step1: Open the Visual Basic Editor (VBE) by pressing Alt+F11.

Step2: In the VBE, click on Insert -> Module to create a new module.

Step3: In the module, enter the following code:

How to Remove the FirstLast Word from Text string in Cell vba 1.png
Function RemoveFirstWord_ExcelHow(ByVal inputString As String) As String
    Dim firstSpaceIndex As Integer
    firstSpaceIndex = InStr(1, inputString, " ")
    If firstSpaceIndex > 0 Then
        RemoveFirstWord_ExcelHow = Mid(inputString, firstSpaceIndex + 1, Len(inputString))
    Else
        RemoveFirstWord_ExcelHow = ""
    End If
 
End Function

Step4: Save the module and return to the Excel workbook.

Step5: In a blank cell, enter the following formula:

=RemoveFirstWord(B1)

Where B1 is the cell that contains the text string you want to remove the first word from.

Step6: Press Enter to display the result.

How to Remove the FirstLast Word from Text string in Cell vba 2.png

4. Remove the Last Word from Text String using User Defined Function with VBA Code

If you also want to remove the last word from a text string in excel using a User Defined Function with VBA code, and you can refer to the above steps, and just using the following code:

How to Remove the FirstLast Word from Text string in Cell vba 2.png
Function RemoveLastWord_ExcelHow(ByVal inputString As String) As String
    Dim lastSpaceIndex As Integer
    lastSpaceIndex = InStrRev(inputString, " ")
    If lastSpaceIndex > 0 Then
        RemoveLastWord_ExcelHow = Left(inputString, lastSpaceIndex - 1)
    Else
        RemoveLastWord_ExcelHow = ""
    End If
End Function

In Cell E1, type the following formula, press Enter key to apply it:

=RemoveLastWord_ExcelHow(B1)
How to Remove the FirstLast Word from Text string in Cell vba 4.png

5. Video: Remove the First/Last Word from Text string in Cell

This video will demonstrate how to remove the first/last word from a text string using a formula and VBA code.

6. Related Functions

  • 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 LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • 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 RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[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 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 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)….

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 remove non numeric characters from a cell

This post explains that how to remove non-numeric characters (numbers) from a text string in one cell in excel 2016/2019/365. How to remove non numeric characters from a cell containing text string with an excel formula. And how to remove alphanumeric characters from a cell with a user defined function in excel VBA.

1. Remove non numeric characters with an Excel Formula

If you want to remove non numeric characters from a text cell in excel, you can use the array formula:

{=TEXTJOIN("",TRUE,IFERROR(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)+0,""))}

Let’s see how the above formula works:

=ROW(INDIRECT(“1:”&LEN(B1))

The ROW function returns the below array list:

{1,2,3,4,5,6,7,8,9}

=MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)

The MID formula will return the below array:

{"e","x","c","e","l","2","0","1","6"}

=IFERROR(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)+0,””)

The array returned by the above MID function add zero for each value in array. If the value is a numeric text, it will be converted to text format. If not, returns empty string. So the IFERROR function returns the below array:

{2,0,1,6}

Last, the TEXTJOIN function join the values in above array returned by the IFERROR function.

Remove non numeric characters with an Excel Formula1

2. Remove non numeric characters using VBA Code

You can create a new function to remove numeric characters from a cell that contain text string in Excel VBA. Just refer to the below steps:

Step1: open visual Basic Editor, then insert a module and name as : RemoveNonNum.

Remove non numeric characters with excel vba1

Step2: click “Insert“->”Module“, then paste the following VBA code into the window:

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

remove non numeric characters from a cell1
Sub RemoveNonNum()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("select one Range that you want to remove non numeric characters", "RemoveNonNum", myRange.Address, Type:=8)
    For Each myCell In myRange
        LastString = ""
        For i = 1 To Len(myCell.Value)
            mT = Mid(myCell.Value, i, 1)
            If mT Like "[0-9]" Then
                tString = mT
            Else
                tString = ""
            End If
            LastString = LastString & tString
        Next i
        myCell.Value = LastString
    Next
End Sub

Step4: back to the current worksheet, then run the above excel macro. Click Run button.

Remove non numeric characters with excel vba4

Step5: select one Range that you want to remove non numeric characters. click Ok button.

remove non numeric characters from a cell2

Step6: Let’s see the last result:

Remove non numeric characters with excel vba4

3. Video: Remove non numeric characters in Excel

This video will demonstrate how to remove non-numeric characters in Excel using a formula or VBA code.

4. Related Formulas

  • Remove Numeric Characters from a Cell
    If you want to remove numeric characters from alphanumeric string, you can use the following complex array formula using a combination of the TEXTJOIN function, the MID function, the Row function, and the INDIRECT function..…
  • Combine Text from Two or More Cells into One Cell
    If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…

5. Related Functions

  • Excel TEXTJOIN function
    The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…
  • 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 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 IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel INDIRECT  function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….

How to extract text after first comma or space

In the previous post, we talked that how to extract substring before the first comma or space or others specific characters in excel. And this post will guide you how to extract text after the first comma or space character in a text string using a formula and VBA code..

1. Extract Text after First Comma or Space Using Formula

If you want to get substring after the first comma character from a text string in Cell B1, then you can create a formula based on the MID function and FIND function or SEARCH function as follows:

=MID(B1,FIND(",",B1)+1,LEN(B1))

or

=MID(B1,SEARCH(",",B1)+1,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)+1

extract text after first comma11

The FIND function returns the position of the first comma character in Cell B1. It returns 7. And then add 1 to get the position of the first character after comma character. The returned value goes into the MID function as its start_num argument.

=MID(B1,FIND(“,”,B1)+1,LEN(B1))

extract text after first comma1

So far, you got the values of the start_num and num_chars arguments from above FIND and LEN formula. And then the MID function extracts a substring based on the starting position and the number of the characters that you want to extract from a text string in Cell B1.

Last, if you want to extract a string after the first space character or others specific characters in a text string in cell B2, then you just need to change the comma character to space character in the above MID function, like this:

=MID(B1,FIND(" ",B1)+1,LEN(B1))
extract text after first comma2

2. Extract Text after First Comma or Space using a User Defined Function with VBA Code

You can use the following VBA code to create a user-defined function in Excel that extracts text after the first comma or space in a cell. Just do the following steps:

Step1: Press Alt + F11 to open the VBA editor in your current worksheet.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBA editor, go to Insert -> Module to create a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Paste the below code into the module. Save the module and close the editor.

How to extract text after first comma or space vba 1.png
Function ExtractTextAfterCommaOrSpace_Excelhow(text As String) As String
    Dim pos As Integer
    pos = InStr(text, ",")
    If pos = 0 Then
        pos = InStr(text, " ")
    End If
    If pos = 0 Then
        ExtractTextAfterCommaOrSpace_Excelhow = ""
    Else
        ExtractTextAfterCommaOrSpace_Excelhow = Trim(Mid(text, pos + 1))
    End If
End Function

Step4: Go back to Excel and enter a cell where you want to use the function. Type the following formula:

=ExtractTextAfterCommaOrSpace_Excelhow(B1)

Step5: press Enter to apply this formula. And the extracted text would be returned.

How to extract text after first comma or space vba 2.png

3. Video: Extract Text after First Comma or Space

This video will demonstrate how to extract text after the first comma or space in Excel using both a formula and VBA code.

4. 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….
  • Extract Text between 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…..
  • 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 ….
  • Extract text before first comma or space
    If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function….

5. Related Functions

  • 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 SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (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 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 Remove Last or Trailing Commas in Excel

This post will guide you how to remove trailing commas from cell in Excel. How do I remove comma character at the end of a cell with a formula in Excel 2013/2016.

1. Remove Trailing Commas using Formula

if you want to remove trailing commas from you Excel cells, and you can create a formula to check for a comma at the end of a string and then removes the final comma character. you can use the IF function, the RIGHT function, the LEFT function and the LEN function to remove the commas from each cell, like this:

=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

You need to type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill handle over to the range of cells that you want to apply this formula.

remove last commas1

2. Remove Last or Trailing Commas using User Defined Function with VBA Code

You can also use a user defined function with VBA code to remove last or trailing commas in Excel. Here are the steps to create and use such a function:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Adding Comma Character at End of Cells vba1.png

Step2: In the Visual Basic Editor window, right-click on any sheet name and select Insert > Module.

Adding Comma Character at End of Cells vba1.png

Step3: In the code window, paste the following VBA code. Press Ctrl + S to save the workbook as a macro-enabled file (.xlsm).

How to Remove Last or Trailing Commas in Excel vba1.png
Function RemoveLastComma_Excelhow(rng As Range) As String
    Dim s As String
    s = rng.Value
    If Right(s, 1) = "," Then
        s = Left(s, Len(s) - 1)
    End If
    RemoveLastComma_Excelhow = s
End Function

Step4: Go back to the worksheet where you have the data with commas.

Step5: In an empty cell, enter the formula:

=RemoveLastComma_Excelhow(A1)

Where A1 is the cell that contains the comma-separated values.

Step6: press Enter key and you will see that the last or trailing comma is removed from each cell.

How to Remove Last or Trailing Commas in Excel vba2.png

3. Video: Remove Last or Trailing Commas

In this video, you will learn how to use the LEFT and LEN functions to extract the text before the last comma, and how to create a User Defined Function with VBA code that can remove the trailing commas.

4. Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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 LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[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 Sort Column by Length of Cell Characters in Excel

This post will guide you how to sort column values by length of the characters in one column in Excel. How do I sort a list of column by character length in Excel 2013/2016.

Sorting by the length of cell characters is a useful feature when you want to quickly identify cells with a certain length of text, such as cells with too much or too little information.

1. Sort Column by Length of Cell Characters using Sort Feature

Assuming that you have a list of data in column A which contain text string with different length. And you want to sort those data by character length in Column A. How do do it. This post will show you how to sort data by length of cell values.

To sort Column by length of Cell characters using Sort function, you still need to use a helper column to calculate the number of characters in Column A using LEN function, then use Sort option to sort those data. Do the following steps:

Step1: enter the following formula based on LEN function in the adjacent Column B to calculate the length of text string in Column A. Then drag the AutoFill Handle in Cell B1 down to other cells to apply this formula.

=LEN(A1)
sort column by length 1

Step2:  go to Data tab in the Excel Ribbon, and click Sort button under Sort & Filter group. And the sort Warning dialog will open.

sort column by length 2

Step3: select Expand the selection option in the Sort Warning dialog box. And click Sort button. Then the Sort dialog box will appear.

sort column by length 3

Step4: select Column B in the dropdown list box of Sort by, and choose Cell Values in the drop down list box of Sort on, choose the sort order as Smallest to largest in the Order drop down list box. Click OK button

sort column by length 4

Step5: you should see that column A has been sorted by the length of Cell values. Now you can delete the helper column.

sort column by length 5

By sorting the column based on the length of cell characters, you can easily find cells with similar content and make changes to your spreadsheet accordingly.

2. Related Functions

  • 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)…

Sort by Second or Third character in a Column

This post will guide you how to sort text string by second or third character in a Column in Excel. You may be want to sort a string in a Cell by the Second or last character in excel, how to quickly do it? You can use the Excel Formula or Text to Column feature to solve this problem. The below will give you the detailed description.

1. Video: Sort by Second Character in a Column

In the below video tutorial, you can learn how to sort by second or third character in a column by using a formula with the RIGHT function or by splitting the text into columns with the Text to Column feature.

2. Excel sort by second character with Formula

To sort test string in a cell by its second character, you can use a combination of the RIGHT function and the LEN function to build a new Excel formula, you can refer to the following generic formula.

=RIGHT(Cell, LEN(Cell)-1)

This formula will remove the first character of the text string in Cell, then you can use the sort feature in Microsoft Excel to sort this column, and then the original column also will be sorted. Just do it following:

Assuming that you want to sort the range A1:A4 in Column A by second character.

Step1: select the cell B1 next to the cell A1 contains that you want to sort

sort string by second character1

Step2: type the following formula in the formula box of Cell B1, then press Enter key.

=RIGHT(A1,LEN(A1)-1)
sort string by second character2

Step3: select Cell B1, then drag the AutoFill Handle down to the cell B2:B4 to apply this formula. You will see that the first character will be deleted.

sort string by second character3

Step4: select the range B1:B4, on the DATA tab, click sort A to Z command under Sort & Filter group.

sort string by second character4

Step5: choose Expand the selection radio button in the Sort Warning window. Then click Sort button.

sort string by second character5

Step6: you will see that the column A and B are sorted by the second character.

sort string by second character6

Step7: you can remove the column B now.

3. Excel sort by second character with Text to Columns

If the text strings are joined by the delimited character, and you want sort text string by the middle characters, then you can use the Text to Columns feature to achieve the result. Or you can use the MID function to extract the middle characters, then use the Sort feature in Excel to sort them.

Just refer to the following steps:

Step1: select the range of cells contain the text string that you want to sort.

sort string by second character7

Step2: on the DATA tab, click Text to Column command, the Convert Text to Columns Wizard window will appear.

sort string by second character7

Step3: choose Delimited radio button in the first step window, click Next button

sort string by second character9

Step4: type the delimited character which is used to join the text string in Cell, click Next button

sort string by second character10

Step5: choose the destination cell reference, then click Finish button.

sort string by second character11

Step6: you will see that the text string is split into three columns, and then you can select the column that contain the middle characters, click Sort A to Z command under DATA tab, the columns are sorted by the middle characters in each cell.

sort string by second character12

This method is only available for the text string that joined by the specific character or delimiter. So if the text string in Cell do not have any common delimiter, you can use the MID function to extract the middle characters, then following the Step5 to sort the text string. You can use the following generic formula:

=MID(A1,FIND("-",A1)+1,1)
sort string by second character13

4. Related Formulas

  • Sort Names by Middle Name in Excel
    Assuming that you have a list of names in your worksheet and you would like to alphabetize by middle name. You can create an excel formula based on the IF function, the ISERR function, the FIND function, and the MID function.…
  • 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 nth word from text string
    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..…
  • 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.…

5. Related Functions

  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[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)…
  • 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 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)….

How to count the number of line breaks in a cell

This post explains that how to count the number of line breaks in a cell using excel formula. How do I count line breaks in a single cell in excel. In the previous post, we talked that how to count specific words in a cell and this post will guide you how to count line breaks characters in a text in a cell.

1. Count Line Breaks in a Cell Using Formula

If you want to count the number of line breaks characters in a cell, you need to create an excel formula based on the LEN function, the SUBSTITUTE function and the CHAR function.

You need to use the SUBSTITUTE function to remove all line break characters in the text in a cell, then using the LEN function to get the length of the text without line break characters. The returned number is then subtracted from the length of the original text in a cell. And then add 1 to get the number of line breaks in the text.

Assuming that you want to count line breaks in the cell B1, you can write down an excel formula as follows:

= LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(10), ""))+1

Let’s see how this formula works:

= LEN(SUBSTITUTE(B1,CHAR(10), “”))

count line break1

This formula will replace all line break characters with empty character in the text in cell B1. And then the number goes into the LEN function to get the length of the text without line break characters.

= LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(10), “”))+1

count line break2

This formula will get the number of line break characters in text in a cell.

2. Count Line Break in the Text String Using Excel VBA

You can count line breaks or new line in a text string using the following VBA code in Microsoft Excel:

Function CountLineBreaksbyExcelHow(rng As Range) As Long
    Dim inputString As String
    inputString = rng.Value
    CountLineBreaksbyExcelHow = UBound(Split(inputString, vbLf))
End Function
How to count the number of line breaks in a cell 20

Or you can use another User Defined Function in VBA to count the number of line break in a text string :

Function CountLineBreaksbyExcelHow2(rng As Range) As Long
    Dim inputString As String
    inputString = rng.Value
    CountLineBreaksbyExcelHow2= Len(inputString) - Len(Replace(inputString, vbLf, ""))
End Function
How to count the number of line breaks in a cell 22

You can then use this function in a cell in Excel to count the line breaks in another cell. For example, if the text string is in cell A1, you can use the following formula in another cell:

= CountLineBreaksbyExcelHow(A1).
How to count the number of line breaks in a cell 21

3. Count number of spaces in a cell

If you want to count the number of spaces in a cell in Microsoft Excel, you can also refer to the above formula to write a newly formula as below:

=LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))
count number of sapces in a cell1

Where A1 is the cell containing the text string.

The LEN function returns the length of a string, while the SUBSTITUTE function replaces all instances of one string with another within a string.

In this case, the SUBSTITUTE function replaces all spaces with an empty string, and the LEN function returns the length of the original string minus the length of the string with all spaces removed. This gives you the total number of spaces in the text string.

4. How do you count the number of dashes in a cell

If you want to count the number of dashes in a cell, you can also use the Len formula in combination with the SUBSTITUTE function, and just need to replace space character as dash character:

=LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))
count number of dashes in a cell1

5. How to Add a Newline in a cell

To add a newline in a cell in Microsoft Excel, you can use the following steps:

Step 1: Select the cell where you want to add the newline.

Step 2: Right-click on the cell and select “Format Cells” from the context menu.

add line break in a cell1

Step 3: In the Format Cells dialog box, select the “Alignment” tab.

Step 4: In the “Alignment” tab, select “Wrap text” in the “Text control” section.

Step 5: Click “OK” to close the Format Cells dialog box.

add line break in a cell1

The cell content will now wrap within the cell and create a newline whenever the text reaches the end of the cell. To manually add a newline, press Alt + Enter within the cell to insert a line break.

add line break in a cell1

6. Conclusion

Counting line breaks or new lines in text within a single cell in Microsoft Excel can be easily achieved using either a formula or a VBA macro. Both methods are effective and efficient, and can save time and effort compared to manually counting line breaks in the text.

7. 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.…
  • Count the number of words in a cell
    If you want to count the number of words in a single cell, you can create an excel formula based on the IF function, the LEN function, the TRIM function and the SUBSTITUTE function. ..

8. 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 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 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)….

Abbreviate Names Or Words in Excel

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn’t any other way to do this task, but you assumed wrong because fortunately there is a way which would let you abbreviate names and words in a matter of seconds unlike doing it manually which consumes a lot of time and leaves you with no satisfying results.

abbreviate names1

So for exploring that way/method, let’s dive into the article.

General Formula:

The array formula to abbreviate different names and words in few seconds is mentionedas follows:

{=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))}

abbreviate names1

Syntax Explanation:

To understand the working of the above formula, we first need to know about each syntax and how they contribute to abbreviating names and words in seconds.

  • TEXTJOIN: The TEXTJOIN function in Microsoft Excel allows you to join two or more strings together, with each value separated by a delimiter. The TEXTJOIN function is an Excel built-in function classified as a String/Text Function.
  • IF: The IF function is one of Excel’s most used functions, allowing you to create logical comparisons between a number and what you anticipate.
  • ISNUMBER: When a cell contains a number, the ISNUMBER function returns TRUE; otherwise, it returns FALSE. ISNUMBER can be used to verify that a cell contains a numeric value or that the output of another function is a number.
  • MATCH: The MATCH is a function used to find a lookup value in a row, column, or table in MS Excel. MATCH allows for approximate and accurate matching and wildcards (*?) for partial matches.
  • CODE: The CODE function in Excel returns a numeric code for a specified character.
  • INDIRECT: The INDIRECT function returns a range reference. This function may generate a reference that will not change if a row or column is added to the worksheet.

Let’s See How This Formula Works:

To abbreviate capital letters text, use this array formula based on the TEXTJOIN function, new in Office 365 and Excel 2019. You may use this method to generate initials from names or acronyms. Because only capital letters can survive this algorithm, the original text must contain capitalized terms. If necessary, you can capitalize words using the PROPER function.

The formula in B2 in the case provided is:

=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

The MID function is used to turn the string into an array of individual letters from the inside out:

=MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)

In this formula section, the operators MID, ROW, INDIRECT, and LEN transform a string into an array of letters.

abbreviate names1

MID delivers an array of all characters in the text.

=CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))

This above array is sent to the CODE function, which returns an array of numeric ASCII codes, one for each letter.

abbreviate names1

=ROW(INDIRECT("65:90")

ROW and INDIRECT are used to generate another numeric array:

{65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90}

abbreviate names1

Note: The digits 65 to 90 correspond to the ASCII codes for all capital letters from A to Z. This array is used as the lookup array in the MATCH function, and the original array of ASCII codes is given as the lookup value.

{=MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)}

abbreviate names1

The MATCH function returns either a number or the #N/A error. Because numbers represent capital letters.

{=IF(ISNUMBER(MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"")}

abbreviate names1

The ISNUMBER function is used with the IF function to filter results. Only characters with ASCII codes between 65 and 90 will be included in the final array, reconstructed using the TEXTJOIN method to produce the final abbreviation or acronym.

Abbreviate Names And Words In MS Excel 2016 Or Older Versions

As in Excel 2016 and earlier versions, the formula discussed above would not work, so the TRIM function is available in Excel 2016 and earlier versions, so we use it to abbreviate names and words.

abbreviate names1

General Formula:

The formula we would use in MS Excel 2016 and earlier versions to abbreviate names and words as follows.

=TRIM(LEFT(Text,1)&MID(Text,FIND(" ",Text&" ")+1,1)&MID(Text,FIND("*",SUBSTITUTE(Text&" ","*,"2))+1,1)

How Does This Formula Work?

From cell A2, if you want to extract the initials, enter this formula in cell B2.

=TRIM(LEFT(A2,1)&MID(A2,FIND(" ",A2&" ")+1,1)&MID(A2,FIND("*",SUBSTITUTE(A2&" "," ","*",2))+1,1))

Here the text string is the string you want to extract the first letters of each word.

When you press the Enter key, all of the first letters of each word in cell A2 are retrieved.

abbreviate names1

Explanation:

  1. The TRIM function eliminates any extra spaces from the text string.
  2. The LEFT(A2,1) function retrieves the first letter of the text string.
  3. MID(A2,FIND(” “,A2&” “)+1,1) retrieves the initial letter of the second word separated by a space.
  4. MID(A2,FIND(“*”,SUBSTITUTE(A2&” “,” “,”*,”2))+1,1) retrieves the initial letter of the third word separated by a space.

NOTE:

This formula only works when three or fewer words are in a cell. You can modify ” “ in the formula to different delimiters.

This formula extracts the initial characters in a case-insensitive manner; if you want the formula to always return in the upper case, include the UPPER function in the formula.

=UPPER(TRIM(LEFT(A2,1)&MID(A2,FIND(" ",A2&" ")+1,1)&MID(A2,FIND("*",SUBSTITUTE(A2&" "," ","*",2))+1,1)))

abbreviate names1

Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel TEXTJOIN function
    The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • 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 INDIRECT function
    The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel CODE function
    The Excel CODE function returns the numeric ASCII value for the first character of a text string.The syntax of the CODE function is as below:= CODE  (text)…
  • 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 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 LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • 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 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)….

 

 

Extract Last Two Words From Multiple Cells

Just assume that you have a few cells containing values/words and you want to extract the last two words from each cell into another separate cell; then you might think that it’s not a big deal; because you would prefer to manually extract the last two words from the cell into another without any need of the formula then congratulations because you are thinking right, but let me add up that it would be a big deal to extract the last two words from the multiple cells to another cell and doing it manually would be a foolish attempt because 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 last the two words from multiple cells 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 last the two words from multiple cells into separate cells :

=MID(B1,FIND("#",SUBSTITUTE(B1," ","#",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))-1))+1,200)

extract last word from multiple cells1

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 last two words/values from multiple cells into separate cells:

  • MID: This function contributes to extracting the number or characters from the given string by starting from the left side.
  • FIND: In Excel, this FIND function contributes to finding out one text inside the other one.
  • LEN: In Excel, this LEN function contributes to finding out the length of the text string.
  • SUBSTITUTE: In excel, this SUBSTITUTE function contributes to replacing the existing text with new text in a text string.
  • B1: this function represents the input value.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and to separate them from the rest of the 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 formula uses the MID function to extract the characters from the second to last space. The MID function accepts three arguments; the number of characters to extract, the starting position, the text to work with.

The text is from column B, and the number of characters can be large enough to ensure that the last two words are taken. The task is to figure out where to start, just after the second-to-last spot. The sophisticated work is mostly done with the SUBSTITUTE function, which accepts an optional input called instance number. This function is used to replace the second to last space in the text with the “#” character, which is then found using the FIND function.

The following snippet figures out how many total spaces are in the text, from which 1 is subtracted.

=LEN(B1)-LEN(SUBSTITUTE(B1," ","")-1

extract last word from multiple cells1

According to the example, the above code would return 5 because there are 6 spaces in the text. As the instance number, this returning number is then placed into the SUBSTITUTE function.

=SUBSTITUTE(B1," ","#",5)

extract last word from multiple cells1

Due to the above placement of the returning number, the SUBSTITUTE function would replace the fifth space character with “#“, now you might be curious about it that why we are using “#” ? so here is the answer that it’s an arbitrary choice you can use any other character too but here is the condition that the chosen character would not appear in the original text.

After this, the FIND Function would locate the “#” character (or whatever character you would use) in the text:

=FIND("#","Extract Multiple Match Values into#Separate Columns")

extract last word from multiple cells1According to the example, the result of the FIND function would be 35, in which 1 is added for getting 36. This is the starting point, and as the second argument, it would go into the MID function.

Extract Last N words from String Using MID Function

In this formula, we have made the adjustments to extract the last 2 words from the cells and can also be generalized to extract the last N words from a cell by replacing the hardcoded 1 in the example with (N-1).

Moreover, if you want to extract many words, you must replace the hardcoded argument in MID, 200, with a larger number. To make sure that the number is large enough, you can use the LEN function, like it is used as follows:

=MID(B1,FIND(“#”,SUBSTITUTE(B1,” “,”#”,LEN(B1)-LEN(SUBSTITUTE(B1,” “,””))-1))+1,LEN(B1))

extract last word from multiple cells1

Related Functions

  • 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 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 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])

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 Cells More Than Certain number of Characters in Excel

This post will guide you how to count the number of cells that more than a certain number of characters in a given range cells using a formula in Excel 2013/2016.How do I count cells with length that greater than a specific number (10) in Excel.

Count Cells that Contain More Than a Certain Number 10

Assuming that you want to count cells that contain more than a certain number of characters in a selected range(A1:A6).In this case, you can use a formula based on the SUMPRODUCT function and the N function.

Enter the following formula in a blank cell, and press Enter key:

=SUMPRODUCT(N(LEN(A1:A6)>10))

Or

= SUMPRODUCT(–(LEN(A1:A6)>10))

count cells more than certain number1

count cells more than certain number2

Note: A1:A6 is the data range that you want to use. LEN(A1:A6)>10 is a condition that that data need to be matched.

Now Let’s see how this formula works:

The LEN function will calculate the length for the selected range of cells. Since multiple values were passed into the LEN function and it should be return multiple results as an array list like this:

count cells more than certain number4

count cells more than certain number3

={8;15;8;6;11;5}

The LEN function will combine with a logic expression “>10”, and it will return multiple results in an array also like below:

count cells more than certain number5

count cells more than certain number6

={FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

Then the above array result need to be converted to ones and zeros through the N function or double subtract operators like below:

=–(LEN(A1:A6)>10)

Or

=N(LEN(A1:A6)>10)

count cells more than certain number7

count cells more than certain number8

Array result is like as below:

={0;1;0;0;1;0}

Last, the above array result contain zeros and ones should be counted by SUMPRODUCT function, it returns 2.

Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • 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 N 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 Convert Uppercase to Lowercase Except the First Letter in Excel

In excel, words can be entered in uppercase, lowercase or mixed. If we want to convert all uppercase to lowercase (except the first letter in some situations), we can use formula with some letter convert related functions to convert them to proper case. This article will introduce you some methods to convert letters from uppercase to lowercase, it can help you to solve your problem.

Precondition:

Prepare below table. To convert uppercase to lowercase for worlds or sentence, actually there are two forms after converting. We can convert uppercase to lowercase but keep the first uppercase for each word; in another way, we can convert uppercase to lowercase but only keep the first uppercase for the first word in the sentence.

Convert Uppercase to Lowercase 1

Part 1: Convert Uppercase to Lowercase Except the First Letter for Each Word

Step 1: In B2 enter the formula =PROPER(A2).

Convert Uppercase to Lowercase 2

For PROPER function, it converts a text or string to proper case, actually it converts the first letter in each word to uppercase, but keeps other letters in lowercase. So, no matter letters are entered in uppercase or lowercase or mixed, it will be finally displayed with the first letter in uppercase with the other letters in lowercase after applying this function.

Step 2: Drag the fill handle down to fill other cells. Verify that uppercase is converted to lowercase properly.

Convert Uppercase to Lowercase 3

Part 2: Convert Uppercase to Lowercase Except the First Letter for the First Word

Step 1: In B6 enter the formula =UPPER(LEFT(A6,1))&LOWER(RIGHT(A6,LEN(A6)-1)).

Convert Uppercase to Lowercase 4

This formula is combined with UPPER and LOWER two functions. It is easy to understand. For UPPER function, keep the first letter from left displaying in uppercase; for LOWER function, keep other letters except the first letter from left displaying in lowercase.

Step 2: Drag the fill handle down to fill other cells. Verify that uppercase is converted to lowercase properly.

Convert Uppercase to Lowercase 5

Related Functions

  • Excel UPPER function
    The Excel UPPER function converts all characters in text string to uppercase.The UPPER function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the UPPER function is as below:= UPPER (text)…
  • Excel LOWER function
    The Excel LOWER function converts all characters in a text string to lowercase.The syntax of the LOWER function is as below:= LOWER  (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 LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[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 Remove Middle Name from Full Name in Excel

When we get a list of full names, we can remove the middle name for short in some cases. As the middle names are different among the full name list so we cannot replace them by space directly in excel. This article will provide you two methods to remove the middle name. User can use Formula to remove the middle name, or can remove it by Find and Replace function.

Prepare a list of full names.

Remove Middle Name from Full Name 1

Remove the Middle Name from Full Name by Formula

Step1: Select a blank cell next to the full name, for example select B1. Enter the following formula into B1:

=TRIM(LEFT(A1,FIND(” “,LOWER(A1),1))) & ” ” & TRIM(MID(A1,FIND(” “,LOWER(A1),FIND(” “,LOWER(A1),1)+1)+1,LEN(A1)-FIND(” “,LOWER(A1),1)+1)) .

Remove Middle Name from Full Name 2

Step2: Click Enter and get the result.

Remove Middle Name from Full Name 3

Step3: Drag the Auto Fill handle to fill B2 and B3. Now all the middle names are removed from the list.

Remove Middle Name from Full Name 4

Remove the Middle Name from Full Name by Find and Replace Function

Step1: Click Ctrl+F to load Find and Replace window.

Step2: Under Replace tab, in Find what textbox enter ‘ * ‘ (a space + * + a space); in Replace with textbox enter ‘ ‘ (a space). Then click Replace All.

Remove Middle Name from Full Name 5

Step3: Check the result. All the middle names are removed.

Remove Middle Name from Full Name 6

Related Functions

  • 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 LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • 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 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 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 LOWER function
    The Excel LOWER function converts all characters in a text string to lowercase.The syntax of the LOWER function is as below:= LOWER  (text)…

How to Count Comma Separated Value in One Cell in Excel

This post will guide you how to count values in a single cell separated by commas with a formula in Excel. How do I count comma separated values in one cell in Excel 2013/2016. Is it possible to have a formula that can count the amount of values separated by commas in a single cells in Excel.

Count Comma Separated Value in One Cell

Assuming that you have a list of data which contain text string values, and each values is separated by comma character. For example, Cell B1 contains “excel,word,access”. I want to know how many values there are in Cell B1. The below method will show you how to count the number of values which are separated by comma character in a single cell.

Step1: you need to select a blank cell or the adjacent cell of Cell B1.

Step2: enter the following formula based on the LEN function and the SUBSTITUTE function in Cell C1, and press Enter key to apply this formula.

=LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1

Or you can use another similar formula to achieve the same result, like this:

=LEN(TRIM(B1))-LEN(SUBSTITUTE(TRIM(B1),",",""))+1

Step3: you would see that the total number of values separated by comma character is calculated in Cell C1.

count value separated by comma1

count value separated by comma2

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 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 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)….

How to Count the Number of Letters or Numbers separately in a Cell in Excel

This post will guide you how to count the number of a given character in a cell in Excel. How do I count only numbers within a single cell excluding all letters and other characters with a formula in Excel. How to count the number of letters excluding all numbers in a given string in Excel.

Count Total Characters in a Cell

Assuming that you have a list of data in range B1:B4, and you want to count the total number of all characters in one cell, you can use a formula based on the LEN function to get it. Like this:

=LEN(B1)

Type this formula in cell C1 and press Enter key on your keyboard, and drag the AutoFill Handle to copy this formula from Cell C1 to range C2:C4.

count number of letters 1

You should notice that the number of all characters in each cell is calculated.

Count Only Numbers in a Cell

If you want only count the total numbers in cells, excluding letters and other specific characters, you can use a formula based on the LEN function and the SUBSTITUTE function. Like this:

=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

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

count number of letters2

Count Only Letters or Other Characters Excluding Numbers

If you want to count only letters and other specific characters in Cells, you can also use another formula based on the LEN function and the SUBSTITUTE function to achieve the result. Like this:

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

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

count number of letters3

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 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 Text between Two Text Strings in Excel

This post will guide you how to extract text between two given text strings in Excel. How do I get text string between two words with a formula in Excel.

1. Extract Text between Two Text Strings

Assuming that you have a list of data in range B1:B5, in which contain text string values. And you need to extract all text strings between two words “excel” and “learning” in cells. You can use a formula based on the MID function, the SEARCH function, and the Len function to extract text between two specified strings. Like this:

=MID(B1,SEARCH("excel",B1)+LEN("excel"),SEARCH("learning",B1)-SEARCH("excel",B1)-LEN("excel"))

Type this formula into a blank cell and press Enter key on your keyboard. And then drag the AutoFill Handle down to other cells to apply this formula.

extract text between two words1

You just need to replace words “excel” and “learning” as you need in the above formula.

You should see that the text string have been extracted between two given works in your data.

2. Related Functions

  • 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 SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (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 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 Remove Prefix and Suffix in Given Cells in Excel

This post will guide you how to remove suffix form a range of cells in Excel. How do I remove prefix from text string in cells with a formula in Excel.

Assuming that you have a list of data in range B1:B5, you want to remove the prefix “www.”  or suffix “.com” from the text string in cells. Let’s see the following introduction.

Remove Prefix of Cells

If you want to remove prefix characters from the text string in range B1:B5, you can use a formula based on the RIGHT function and the LEN function. Like this:

=RIGHT(B1,LEN(B1)-4)

Type this formula into a blank cell and press Enter key on your keyboard. And then drag the AutoFill Handle down to cells to apply this formula.

Let’s see how this formula works:

Number 4 is the length of your prefix characters. And the Len function returns the length of the cell B1. The RIGHT function will remove the leftmost four characters (prefix) and return the rest characters of the text string.

remove prefix in given range1

Remove Suffix of Cells

If you want to remove the suffix characters (.com) from the given range of cells in your worksheet, you can use a formula based on the LEFT function and the Len function. Like this:

=LEFT(B1,LEN(B1)-4)

Type this formula into a blank cell and press Enter key on your keyboard. And then drag the AutoFill Handle down to cells to apply this formula.

remove prefix in given range2