Tag: Excel SUBSTITUTE

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 extract text after the second or nth specific character (space or comma)

In the previous post, we talked that how to extract text after the first occurrence of the comma character in excel. And this post explains that how to get a substring after the second or nth occurrence of the comma or space character in excel.

1. Extract Text after The Second or Nth Specific Character

If you want to extract text after the second or nth comma character in a text string in Cell B1, you need firstly to get the position of the second or nth occurrence of the comma character in text.

So you can use the SUBSTITUTE function to replace the second comma character with the hash character, then using the FIND function to look for the first hash character in text returned by substitute function, the returned value is the position of the second occurrence of the comma character in text.

Last, you can use the MID function to extract a substring that you want to extract.

So you can create a formula based on the MID function, the FIND function and the SUBSTITUTE function as follows:

=TRIM(MID(B1,FIND("#",SUBSTITUTE(B1,",","#",2))+1,255))

Let’s see how this formula works:

=SUBSTITUTE(B1,”,”,”#”,2)

extract text before second comma1

This function replaces the second comma character with hash character in cell B1, and the returned result goes into the FIND function as its within_text argument.

=FIND(“#”,SUBSTITUTE(B1,”,”,”#”,2))+1

extract text before second comma2

The FIND function will return the position of the first occurrence of the hash character in within_text string returned by the SUBSTITUTE function. Then add 1 to get the starting position after the second occurrence of the comma character in text. And it returns 11. It is fed into the MID function as its start_num argument.

=MID(B1,FIND(“#”,SUBSTITUTE(B1,”,”,”#”,2))+1,255)

extract text after second comma1

The MID function will extracts 255 characters from a text string in Cell B1, starting at the 11character.

=TRIM()

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

If you want to extract a substring after the third occurrence of the comma character, you can use the following formula:

=TRIM(MID(B1,FIND("#",SUBSTITUTE(B1,",","#",3))+1,255))
extract text after second comma2

2. Extract Text after The Second or Nth Specific Character using User Defined Function with VBA

You can use the VBA Split function to split a string into an array of substrings based on a specified delimiter, and then extract the desired substring based on its index in the array.

You can use a user-defined function that extracts the substring after the third occurrence of the comma character, just do the following steps:

Step1: Press the Alt + F11 keys on your keyboard to open the VBA Editor.

Step2: In the VBA Editor, insert a new module by clicking on “Insert” in the top menu and then selecting “Module“.

Step3: Copy the VBA code that you want to run and paste it into the new module.

How to extract text after the second or nth specific character vba1.png

Step4: Save the module by clicking on “File” in the top menu and then selecting “Save“.

Step5: in a blank cell, type the following formula:

=ExtractSubstring_ExcelHow(B1)

Step6: Press Enter to apply the formula and see the result in the given cell.

How to extract text after the second or nth specific character vba2.png

The function should now be applied to the selected cell and the output should be the desired substring after the third occurrence of the comma character.

3. Video: Extract Text after The Second or Nth Specific Character

This video will show you how to extract a substring after the third occurrence of the comma character 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….
  • Extract text after first comma or space
    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 ….
  • Extract text before the second or nth specific character
    you can create a formula based on the LEFT function, the FIND function and the SUBSTITUTE function to Extract text before the second or nth specific character…

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

How to Get the Position of the nth Occurrence of a Character in a Cell

In the previous post ,we talked that how to get the position of the last occurrence of a character in a cell, and sometimes, you may be want to know the position of the 2th, 3th or nth occurrence of a character in a text string in excel. and this post will guide you how to find the 2th, 3th, or nth occurrence of a character in a text string using excel formula and use defined function.

1. Get the Position of the Nth Occurrence of a Character using Excel Formula

If you want to get the position of the nth occurrence of a character using a excel formula, you can use the FIND function in combination with the SUBSTITUTE function. For example, to get the position of the 2th occurrence of the character “e” in Cell B1, you can create the following formula:

=FIND("#",SUBSTITUTE(B1,"e","#",2))

The SUBSTITUTE function will replace the 2th occurrence of the character “e” with the hash character. So it returns another text string as “exc#l”.

Get the position of the nth using excel formula1

The FIND function look up the hash character in the text string returned by the SUBSTITUTE function, and it returns the position of the first hash character in string “exc#l”. And it should be the position of the 2th occurrence of the character “e” in Cell B1.

Get the position of the nth using excel formula2

2. Get the Position of the Nth Occurrence of a Character using User Defined Function

You can also create a new user defined function to get the position of the nth occurrence of a specific character or string in Excel VBA:

Step1: click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

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

Step2: click “Insert” ->”Module” to create a new module named as: getNthPosition

Get the position of the nth using excel vba1

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

Get the position of the nth using excel vba1
Function getNthPosition(sFindValue As String, sTextString As String, N As Integer) As Integer
    Dim i As Integer
    Application.Volatile
    getNthPosition = 0
    For i = 1 To N
            getNthPosition = InStr(getNthPosition + 1, sTextString, sFindValue)
        If getNthPosition = 0 Then Exit For
    Next
End Function

Step4: back to the current workbook, then enter the below formula in Cell C1:

=getNthPosition("e",B1,2)
Get the position of the nth using excel vba4

3. Video: Get the Position of the Nth Occurrence of a Character

In this video, you will learn a formula and VBA code that can be used to get the position of the Nth occurrence of a character in a string.

4. Related Formulas

  • Get the position of Last Occurrence of a value in a column
    If you want to find the position number of the last occurrence of a specific value in a column (a single range), you can use an array formula with a combination of the MAX function, IF function, ROW function and INDEX Function.
  •  Get the position of Last Occurrence of a character or string in a cell
    If you want to get the position of the last occurrence of a character in a cell, then you can use a combination of the LOOKUP function, the MID function, the ROW function, the INDIRECT function and the LEN function to create an excel formula.…
  • 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…
  • 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…
  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Get the First Row Number in a Range
    If the ROW function use a Range as its argument, it only returns the first row number.You can also use the ROW function within the MIN function to get the first row number in a range. You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range.…
  •  Get the Last Row Number in a Range
    If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…

5. Related Functions

  • 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 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 Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string. The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function. = FIND(find_text, within_text,[start_num])…

How to Remove Quotes for Text or Strings in Excel

When we importing data from other source into Excel we may import quotes or quotations from initial file. And we want to remove these quotes from current file, if we remove them one by one it needs a lot of work, so we try to find a simple way to remove all of them by one time. This article will show you two ways of removing quotes, by SUBSITITUTE function or Replace function in tool bar, you can select one you like to implement.

1. Video: Remove quotes for Text or Strings in Excel

This video tutorial will demonstrate a simple and efficient method for removing quotes from text or strings in Excel, making your data cleaner and easier to work with.

2. Remove Quotes by SUBSITITUTE Function

We may already have learned about SUBSITITUTE function in previous article. It can be used for replacing specific strings by others. If the quotation marks are always at the begining and end of the text string, you can use the SUBSTITUTE functio to remove them. Just do the following steps:

Step1: select one blank cell B2 where you want to place the result. then type the following formula:

=SUBSTITUTE(A1,""""," ")
How to Remove Quotes 2

In this case we want remove quotes so we can just replace quotes with space. So enter “” as old text, and leave a space as new text.

Step2: Click Enter, then we can find the quotes are removed.

How to Remove Quotes 3

Step3: you can drag the AutoFill Handle down to cell B2 to B3 to apply this formula. Now we can see that all quotes should be removed.

How to Remove Quotes 4

Step4: If we only want to remove the second quote, we can add a new parameter into the formula. Type the following formula: 

=SUBSTITUTE(A1,""""," ",2)
How to Remove Quotes 5

From the above screenshot, you can see that only second quotation marks be removed.

3. Remove Quotes by Replace Function in Tool Bar

This way is much easier to learn for the users that are not familiar with SUBSITITUTE function in Excel. If you have a lot of text to remove quotes, you can use the Find and Replace feature to quickly remove them. Just see the following steps:

Step1: Click on Edit->Find->Replace in tool bar. You can also directly enter Ctrl+H simultaneously on keyboard to load Replace window.

How to Remove Quotes 6

Step2: In Replace window, enter quote “ in Find what field, and enter space (just click space on keyboard) in Replace with field.

How to Remove Quotes 7

Step3: Click Replace All button to replace all quotes. All quotes are removed.

How to Remove Quotes 8

4. Remove Quotes for Text or Strings with VBA Code

You can remove quotes from text or strings in Excel using VBA code by using the Replace function to replace all occurrences of quotes with nothing. Just do the following steps:

Step1: Open your Excel workbook and press Alt + F11 to open the Microsoft VBA editor.

Remove Quotes for Text or Strings with VBA Code 1

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

Remove Quotes for Text or Strings with VBA Code 2

Step3: Copy and paste the following code into the new module (Module1), then save the module and go back to the Excel workbook.

remove quotes for text with vba code1.png
Sub RemoveQuotes_excelhow()
    Dim targetRange As Range
    Dim cell As Range
    
    ' Prompt the user to select a range of cells to remove quotes from
    On Error Resume Next
    Set targetRange = Application.InputBox("Select a range of cells to remove quotes from", Type:=8)
    On Error GoTo 0
    
    ' Exit if the user cancels or does not select a range
    If targetRange Is Nothing Then Exit Sub
    
    ' Loop through each cell in the target range and remove quotes
    For Each cell In targetRange
        cell.Value = Replace(cell.Value, Chr(34), "")
    Next cell
End Sub

Step4: go to the Developer tab (if you don’t see the Developer tab, go to File > Options > Customize Ribbon and select the Developer checkbox under Main Tabs) and click on Macros Command, then Select RemoveQuotes Macro name, clicking Run command.

remove quotes for text with vba code2.png

Step5: select a range of cells to remove quotes form (e.g. A1:C4). Click on Ok button.

remove quotes for text with vba code3.png

Step6: The code will loop through each cell in the selected range of cells and remove all quotation marks using the Replace function.

remove quotes for text with vba code4.png

5. Conclusion

There are various methods to remove quotes from text or strings in Excel, including using a formula or VBA code. The formula approach uses the SUBSTITUTE function to replace the quotes with an empty string, while the VBA code method loops through a specified range of cells and replaces the quotes with an empty string using the Replace function.

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

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

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

How to Remove All Asterisk Characters from Cells in Excel

Sometimes when we opening an excel spreadsheet to check data, we may notice that there are some asterisks exist in texts for security or other reasons. If you feel these special characters are invalid you can remove them from cells.

To remove all asterisks, you can directly remove them manually by holding Backspace, it is very troublesome obviously, we need to find some simple ways to remove them quickly. To help you to solve your problem, we prepare this free tutorial, you can remove asterisk by typing a formula or applying Find and Replace feature in excel. We will introduce the two ways in steps and make sure you can learn them clearly.

Precondition:

See screenshot below. There are some asterisk characters exist in texts. Let’s remove these special characters now.

How to Remove All Asterisk Characters1

1. Remove All Asterisk Characters by Formula

Step 1: In B2 enter the formula:

 =SUBSTITUTE(A2,"*","").
How to Remove All Asterisk Characters2

Step 2: Click Enter to get result. Verify that asterisk characters are removed.

How to Remove All Asterisk Characters3

Step 3: Drag the fill handle down till then last cell in the list. Verify that all asterisk characters from the original list are removed in the new list.

How to Remove All Asterisk Characters4

2. Remove All Asterisk Characters by Find and Replace Feature

In method1 we remove all asterisk characters by SUBSITITUE function in excel. It can be seen a function for replacement. So, we can use excel built-in feature Find and Replace function to remove asterisk characters as well.

Step 1: Press Ctrl+F to trigger Find and Replace dialog. You can also press Ctrl+H to trigger Find and Replace dialog and directly enter Replace tab. You can also click Home->Find & Select->Replace to load Find and Replace.

How to Remove All Asterisk Characters5

Step 2: Click Replace tab, then enter ‘~*’ into Find what textbox, keep nothing in Replace with, then click Replace All.

How to Remove All Asterisk Characters6

Step 3: Verify that all asterisk characters are removed.

How to Remove All Asterisk Characters7

3. How to remove hashtags in excel

If you want to remove hashtags or other symbols from cells in Excel, you can use the “SUBSTITUTE” function to get rid of hashtags or symbols. The syntax of the function is as follows:

=SUBSTITUTE(original text, text to replace, replacement text)

In this case, the original text is the cell that contains the hashtags, the text to replace is “#” and the replacement text is blank (""). To remove hashtags, you can write the following formula:

=SUBSTITUTE(A1, "#", "")
How to Remove All Asterisk Characters from Cells in Excel20

where A1 is the cell that contains the hashtags. You can then copy and paste the formula to other cells to remove hashtags from multiple cells.

Alternatively, you can also use the “FIND” and “LEFT” functions to remove hashtags. This method involves finding the position of the first hashtag and then extracting all the text to the left of the hashtag, effectively removing the hashtag itself.

4. Conclusion

Removing all asterisk characters from cells in Excel can be easily achieved using various functions and techniques. The “SUBSTITUTE” function can be used to replace all instances of the asterisk character with an empty string. Alternatively, the “CLEAN” function can be used to remove all non-printable characters, including asterisks, from cells. These methods allow users to effectively remove a character or all special characters, such as asterisks, from their data.

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

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel.

Suppose that you have a few cells containing few values and you want to find and replace two or three values; then you might think that it’s not a big deal; because you would prefer to do it with the help of the Find and Replace built-in feature of excel, then congratulation because you have supposed to choose the right way for this task.

There isn’t any doubt that you can find and replace values in excel by its Find and Replace built-in feature, which works entirely perfect for two or three values, but when you would have the bulk of values, and you want to find and replace them, then doing this task manually by the aid of this Find and Replace built-in feature would be a stupid decision because you would get tired of it and would never complete your work on time.

But there isn’t any need to worry about it because, luckily, there are a few effective methods for finding and replacing multiple values in a few seconds.

After reading this article carefully, you would get to know the several ways to find and replace multiple words, individual characters, or strings so that you can select the best one according to your needs and ease.

find and replace multiple values1

So let’s get straight into it!

Find and Replay Multiple Values using Formula

 General formula:

The formula given below would help you out for finding and replacing multiple values within few seconds:

=SUBSTITUTE(SUBSTITUTE(B2,INDEX(finding_values,1),INDEX(replacing_values,1)),INDEX(finding_values,2),INDEX(replacing_values,2))

or

=SUBSTITUTE(SUBSTITUTE(B2,finding_values,replacing_values), finding_values, replacing_values))

find and replace multiple values1

Syntax Explanation:

Before we dive into the formula for getting the job done effectively, we need to understand each syntax so that we can know how each syntax helps to find and replace the multiple values:

  • SUBSTITUTE: This function lets users replace current text in a text string with new text when they desire to replace text based on its content rather than its position.
  • INDEX: The INDEX function in Excel is used to repeat characters a specified number of times.
  • B2: This is the input value.
  • Find: This command specifies the text to be found in the input range.
  • Replace: It represents the text that is being replaced.
  • The comma sign (,): is a separator that may separate a list of values.
  • Parenthesis (): The primary purpose of the parenthesis () symbol is to organize the components.

Let’s See How This Formula Works:

As I said above, there are a few ways to find and replace multiple values in Excel in a matter of seconds, and one of the most popular and simple methods is to utilize the nested SUBSTITUTE function.

The logic of this formula is very simple: you write a few individual functions to replace an old value with a new one. Then you nest those functions one inside the other so that each subsequent SUBSTITUTE uses the output of the previous SUBSTITUTE to find the next value.

=SUBSTITUTE(SUBSTITUTE(Text_values,finding_values,replacing_values), finding_values, replacing_values))

Assume you wish to replace the region names in the B2:B9 list of places with the replacing names.

For getting the desired output, input the old values in E2:E3 and the new values in F2:F3, as seen in the image and formula below. Then, in E5, enter the following formula:

= SUBSTITUTE(SUBSTITUTE(B2, E2, F2), E3,F3)

You’ll have completed all of the replacements at once:

Please remember that the above method only works in Excel 365, which supports dynamic arrays.

In pre-dynamic versions of Excel 2016, Excel 2019, and prior,type the following formula:

= SUBSTITUTE(SUBSTITUTE(B2, $E$2, $F$2), $E$3,$F$3)

Also, note that in this case, we lock the replacement values with absolute cell references to ensure that they do not shift when copying the formula down.

find and replace multiple values1

The SUBSTITUTE function is case-sensitive, so you must type the old values (old text) in the same letter case as the original data.

This method has a significant drawback: It should only be used for a limited number of find/replace values. This nested function becomes extremely difficult to manage when you have dozens of values to replace.

Benefits: Simple to deploy; compatible with all Excel versions

Find and replace multiple values using XLOOKUP Function

The XLOOKUP function is very efficient and helpful while replacing the entire cell content rather than just a portion of it.

Assume you have a list of region in column B and want to replace all the “East” or “West” regions as “northeast” or “northwest” values. Enter the following formula in B2:

=XLOOKUP(B2,$E$2:$E$3,$F$2:$F$3,B2)

The formula does the following when translated from Excel to human language:

It searches the B2 value (lookup value) in E2:E3 (lookup array) and returns a match from F2:F3 (return array). If the original value cannot be retrieved, take it from B2.

find and replace multiple values1

Because the XLOOKUP function is only accessible in Excel 365. However, you can easily replicate this behavior using a combination of IFERROR or IFNA and VLOOKUP:

=IFNA(VLOOKUP(B2,$E$2:$F$3,2,FALSE),B2)

find and replace multiple values1

Unlike the SUBSTITUTE, the XLOOKUP and VLOOKUP functions are both not case-sensitive, which means they search for lookup values regardless of letter case.

 Find And Replace Multiple Values with VBA Macro

Assuming that you have a list of data in range B1:B6, and you want to find multiple values and replace those value with different values. For example, find “excel” string and replace its as excel2013, and find “word’’ string and replace its as word2013, and so on. How to achieve it. You should use an excel VBA macro to quickly find and replace multiple values. Just do the following steps:

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

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

find replace multiple values1

Sub ReplaceMulValues()
    Dim myRange As Range, myList As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one range to be searched", "Find And Replace Multiple Values", myRange.Address, Type:=8)
    Set myList = Application.InputBox("select two column range where find/replace pairs are:", "Find And Replace Multiple Values", Type:=8)
    For Each cel In myList.Columns(1).Cells
        myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

find replace multiple values2

#6 Select one range to be searched

find replace multiple values3

#7 select two column range where find/replace pairs are

find replace multiple values4

#8 let’s see the result.

find replace multiple values5

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 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 VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel XLOOKUP function
    This tutorial will show you how to filter out or extract the top n values from the list having few values using Filter function or XLookup function in Excel The syntax of the xlookup function is as below:=XLOOKUP(lookup value, lookup array, return array, [if not found], [match mode], [search mode])…

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 Convert Date Format from Dot MM.DD.YYYY to Slash MM/DD/YYYY in Excel

We often enter dates in excel tables, and we can enter current date by quickly press ‘Ctrl+;’ simultaneously. If in some cases we enter a date format that is not what we want, we have to switch the date format manually, for example if the date format is DD.MM.YYYYY, but we want to use the slash to display date like DD/MM/YYYY, how can we do? This article will show you two methods to convert the data format by formula and VBA code, you can select one you like to convert date format.

Convert Date Format from Dot to Slash by Formula

Frist, we prepare a list of dates in worksheet. See screenshot below.

Convert Date Format from Dot to Slash 1

Step 1: Actually, to convert the separator you just need to replace DOT by SLASH in date format, so we can use SUBSTITUTE function here. In B1, enter the formula =SUBSTITUTE(A1,”.”,”/”).

Convert Date Format from Dot to Slash 2

Step 2: Click Enter to get the result.

Convert Date Format from Dot to Slash 3

Step 3: Fill the following cells A2:A5 by dragging the fill handle. Then date format is displayed as DD/MM/YYYY for all entered dates properly.

Convert Date Format from Dot to Slash 4

Convert Date Format from Dot to Slash by VBA Code

Step 1: Click Developer tab->Visual Basic or Alt+F11 to load Microsoft Visual Basic for Applications window. You can also right click on current sheet1 tab and select View Code to open Microsoft Visual Basic for Applications.

Convert Date Format from Dot to Slash 5

 Step 2: Enter below code into the window.

Private Sub Worksheet_Change(ByVal MyRange As Range)

    On Error Resume Next

    Application.EnableEvents = False

    If Not (Intersect(MyRange, Me.Range("A1:A5")) Is Nothing) Then

        MyRange.Value = Replace(MyRange.Value, ".", "/")

    End If

    Application.EnableEvents = True

End Sub

Screenshot below:

Convert Date Format from Dot to Slash 6

Step 3: Save above code and close Microsoft Visual Basic for Applications. Then in A1, enter a date with format DD.MM.YYYY, then click Enter in the cell, you can find that date format is changed to DD/MM/YYYY directly.

Notes:

  1. Above code is only activated for range A1:A5.
  2. Be aware that range A1:A5 is customed, you can expand the range and make sure the range can cover all entered dates.

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


How to Remove Specific Character from Text Cell in Excel

This post will guide you how to remove a specific character from text cell in Excel. How do I remove certain character from a cell with a formula in Excel 2013/2016.

Assuming that you have a list of data in range B1:B5, in which contain text string values. And you want to remove a specific character if it appears in a given cell. For example, if wish to remove all hash (#) character from your range, how to do it. This post will show you two methods to remove specific character.

Method1: Remove Specific Character Using Find and Replace function

Step1: select one range where you want to remove certain character. Such as: B1:B5.

remove specific character from text string1

Step2: go to Home Tab, and click on the Find & Select command under Editing group. And choose Replace from the context menu. And the Find and Replace dialog will open.

remove specific character from text string2

Step3: Type “#” into the Find What text box, and keep the Replace with text box as blank.

remove specific character from text string3

Step4: click on Replace All button. And a prompt dialog will tell you that how many characters are replaced in your selected range of cells. Click Close button to close the Find and Replace dialog box.

remove specific character from text string4 remove specific character from text string5

Method2: emove Specific Character Using Formula

You can also use an Excel formula based on the SUBSTITUTE function and the CHAR function to accomplish the same result of removing specific character. Like this:

=SUBSTITUTE(B1,CHAR(35),””)

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 specific character from text string6

You would see that this formula can be used to remove all hash characters from a cell.

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 CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value). The syntax of the CHAR function is as below: =CHAR(number)….

 

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 Remove Line Breaks (Carriage Returns) in Excel

This post will explain that how to remove line breaks in your data set in Excel. How do I remove carriage returns or line breaks with a formula in Excel. How to remove line breaks with VBA Macro in Excel. How to remove line breaks with find and Replace feature in Excel 2010/2013/2016.

When you press Alt + Enter keys on your keyboard in a Cell, it will generate a line break. And if you press multiple Alt + Enter keys on your keyboard, and it will generate multiple line breaks in your active Cell. And you may be want to remove all line breaks in a selected range in Excel. How to do it. Here are the methods to remove line breaks in your worksheet.

Remove Line Breaks with Formula

If you want to remove line breaks from a range of cells, you can use a formula based on the SUBSTITUTE function and CHAR function. And the formula is as below:

=SUBSTITUTE(B1,CHAR(10),", ")

This formula will remove all line breaks in Cell B1, and replacing them with commas. And the CHAR(10) function will return a line break character.

You need to type this formula into a blank cell and press Enter key to apply this formula. And then drag the AutoFill Handle over other cells to apply it to remove all line breaks.

remove line breaks1

Remove Line Breaks with Find And Search Feature

You can also use Find And Search function to remove line breaks. And you can replace all line breaks with a new character or a blank character in Replace function. Here are the steps to remove line breaks in Replace function in Excel:

#1 Select the range of cells that you want to remove line breaks.

remove line breaks2

#2 go to HOME tab, click Find & Replace command under Editing group. And select Replace from the drop down menu list. And the Find and Replace dialog will open.

remove line breaks3

#3 you need to hold down the Alt key in your keyboard, and then type numbers 010 in the Find what text box. And type a character or a blank character in Replace with text box. Click Replace All button.

remove line breaks4

#4 All of the line breaks would be removed in the selected range of cells.

remove line breaks5

Remove Line Breaks with VBA Macro

You can also use an Excel VBA Macro to achieve the same result of removing multiple line breads in Microsoft Excel. Here are the steps to remove line breaks with VBA code:

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

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

remove line breaks6

Sub RemoveLineBreaks()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to remove line breaks", "RemoveLineBreaks", myRange.Address, Type:=8)
    For Each myCell In myRange
        myCell.Value = Replace(myCell.Value, Chr(10), "，")
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

remove line breaks7

#6 Select one Range that you want to remove line breaks. click Ok button.

remove line breaks8

#7 let’s see the result:

remove line breaks9

Video: Remove Line Breaks

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 CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value). The syntax of the CHAR function is as below: =CHAR(number)….

See Also:

 

 

How to Convert DD-MM-YYYY text string or date to a standard date format in Excel

This post will guide you how to convert dd-mm-yyyy test string or a date to a standard date format mm/dd/yyyy with a formula in Excel.

Convert Text DD-MM-YYYY to Date Format

Assuming that you have a list of data in range B1:B4 which contain text dates. And you need to convert these text dates to the standard date format mm/dd/yyyy. How to achieve it. You can use a formula based on the SUBSTITUTE function to achieve the result. Like this:

=SUBSTITUTE(B1,””-”,”/”)

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

convert ddmmyyy test to date format1

Video: Convert DD-MM-YYYYY to Standard Date Format

How to convert Column Letter to Number in Excel

This post will guide you how to convert column letter to number in Excel. How do I convert letter to number with a formula in Excel. How to convert column letter to number with VBA macro in Excel. How to use a User Defined Function to convert letter to number in Excel.

Convert Column Letter to Number with a Formula

If you want to convert a column letter to a regular number, and you can use a formula based on the COLUMN function and the INDIRECT function to achieve the result.

For example, you need to convert a column letter in Cell B1 to number

Just like this:

=COLUMN(INDIRECT(B1&1))

Type this formula into a blank cell such as: C1, and press Enter key, and then drag the AutoFill Handle over to other cells to apply this formula.

convert column letter to number1

The INDIRECT function will convert the text into a proper Excel reference and then pass the result to Column function to get the column number for the reference.

Convert Column Letter to Number with VBA Macro

You can also use an Excel VBA Macro to achieve the result of converting column letter into its corresponding numeric value. Just do the following steps:

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

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

convert column letter to number2

Sub ConvertColumnLetterToNumber()
    Dim myRng As Range
    Dim cNum As Double
    Set myRng = Application.Selection
    Set myRng = Application.InputBox("select one range that contain column letters", "ConvertColumnLetterToNumber", myRng.Address, Type:=8)

    For Each myCell In myRng
            cNum = Range(myCell & 1).Column
            myCell.Resize(1).Offset(0, 1) = cNum
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

convert column letter to number3

#6 select one range that contain column letters, such as B1:B4

convert column letter to number4

#7 let’s see the result:

convert column letter to number5

Convert Column Letter to Number with User Defined Function

Excel does not have a built in formula to convert column letter to a numeric number, but you can write down a User Defined Function to achieve the result. Just do the following steps:

#1 repeat the above step 1-3

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

convert column letter to number6

Function ConvertLetterToNum(ColumnLetter As String) As Double
    Dim cNum As Double
        
    'Get Column Number from Alphabet
    cNum = Range(ColumnLetter & "1").Column
    
    'Return Column Number
    ConvertLetterToNum = cNum
End Function

#3 back to the current worksheet, then type the following formula in a blank cell. press Enter key.

=ConvertLetterToNum(B1)

#4 select the cell C1, and  drag the AutoFill Handle over to other cells to apply this formula.

convert column letter to number7

Convert Number to Column Letter

If you want to convert column number to an Excel Column letter, you can use another formula based on the SUBSTITUTE function and the ADDRESS function. Just like this:

=SUBSTITUTE(ADDRESS(1,C1,4),"1","")

Type this formula into Cell D1, and press Enter key. And then drag the AutoFill handle over to other cells to apply this formula.

Let’s see the last result:

convert column letter to number8

Video: Convert Column Letter to Number

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 ADDRESS function
    The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
  • 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 COLUMN function
    The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

Removing Dash Characters in Excel

This post will guide you how to remove all dash characters from a text string in one cell in Excel. How to remove dashes from social security numbers in a cell with a formula in Excel.

Removing Dash Characters

If you want to remove all dash characters in one cell in Excel, you can use a formula based on the SUBSTITUTE function.

For example, you want to remove dashes from text string in one cell (B1), you can write down the following formula:

=SUBSTITUTE(B1,”-”,””)

You need to type this formula into a blank cell, and then press Enter key in your keyboard.

remove dash characters1

You will see that all dash characters in cell B1 has been removed.

 

Remove Dash Characters with VBA Code

You can also use an Excel VBA macro to remove all dash characters from text string in a range of cells in Excel. Just do the following steps:

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

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

remove dash characters2

Sub RemoveDashChar()
    Dim R As Range
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to remove dashes", "removedashchar", myRange.Address, Type:=8)
    For Each R In myRange
        R.Value = VBA.Replace(R.Value, "-", "")
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

remove dash characters3

#6 select one range that you want to remove dash characters

remove dash characters4

#7 let’s see the result.

remove dash characters5

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

Insert The File Path and Filename into Cell

This post will guide you how to insert the file path and filename into a cell in Excel. Or how to add a file path only in a specified cell with a formula in Excel. Or how to add the current workbook name into a cell in Excel. How do I insert the current worksheet name into a Cell with a formula.

Insert File Path and Filename into Cell

If you want to insert a file path and filename into a cell in your current worksheet, you can use the CELL function to create a formual. Just like this:

=CELL("filename")

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

insert filepath filename in cell1

This fromula will return the entire path, filename, and tab name of the current worksheet.

If you want to strip out the brackets and the sheet name, you can use the following formula based on the SUBSTITUTE function, the LEFT function, the CELL function and the FIND function:

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

insert filepath filename in cell2

Insert Filename Only into Cell

If you only want to insert the workbook name into a cell, you can use a formula based on the MID function, the SEARCH function, and the CELL function. Just like this:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

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

insert filepath filename in cell3

You will see this formula will return only the current workbook name in Cell.

Insert Filepath Only into Cell

If you just want to insert the File Path into a cell, you can create a formula based on the LEFT function, the Cell function,and the FIND function. Just like this:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

Type this formula into a blank cell and then press Enter key to apply this formula.

insert filepath filename in cell4

This formula will return only the File path of the current workbook.

Insert Worksheet Name into Cell

If you want to get the worksheet name only and then insert it into one cell, you can create a formula based on the RIGHT function, the LEN function, the CELL function and the FIND function. Just like this:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))

Type this formula into a blank cell and then press Enter key to apply this formula.

insert filepath filename in cell5

This formula will return only the current worksheet name in a cell.

Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.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)…
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • 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 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 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)…
  • 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])…