Tag: Excel MID

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 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 Convert Military Time to Standard Time in Excel

This post will guide you how to convert military time to standard time with a formula in Excel. How do I convert from military time 17:29:30 to regular time 5:29:30PM in Excel.  How do I Convert Standard time to military time with a formula in Excel.

1. Convert Military Time to Standard Time using Formula

Assuming that you have a list of data in range B1:B4, in which contain military time. And you wish to convert those times to standard time. How to do it.

You can a formula based on the TIMEVALUE function, the LEFT function, the MID function and the RIGHT function. The formula is similar to the below:

=TIMEVALUE(LEFT(B1,2)&":"&MID(B1,3,2)&":"&RIGHT(B1,2))

Step1: You need to type this formula into a blank cell (at this example, we select Cell C1) and press Enter key, then copy this formula from cell C1 to range C2:C4.

convert military time to standard1

Step2: Then you need to select range C1:C4, and right click on it, select Format Cells from the popup menu list. the Format Cells dialog will open.

convert military time to standard2

Step3: Switch to Number tab in the Format Cells dialog box, and click Time from the Category list box, then select the standard time type in the Type list box. Click Ok button.

convert military time to standard3

Step4: Let’s see the last result:

convert military time to standard4

2. Convert Military Time to Standard Time using User Defined Function with VBA Code

If you want to convert Military Time to Standard Time using a User-Defined Function with VBA code, you can follow these steps:

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, click on Insert -> Module to insert a new module.

Adding Comma Character at End of Cells vba1.png

Step3: n the new module, paste the following code. Save the module and return to the Excel workbook.

How to Convert Military Time to Standard Time in Excel vba 1.png
Function ConvertMilitaryToStandard_ExcelHow(MilitaryTime As String) As String
    Dim Hour As Integer
    Dim Minute As String
    Dim Second As String
    Dim StandardTime As String
    
    Hour = Left(MilitaryTime, 2)
    Minute = Mid(MilitaryTime, 3, 2)
    Second = Right(MilitaryTime, 2)
    
    If Hour = 0 Then
        StandardTime = "12:" & Minute & ":" & Second & " AM"
    ElseIf Hour < 12 Then
        StandardTime = Hour & ":" & Minute & ":" & Second & " AM"
    ElseIf Hour = 12 Then
        StandardTime = "12:" & Minute & ":" & Second & " PM"
    Else
        StandardTime = Hour - 12 & ":" & Minute & ":" & Second & " PM"
    End If
    
    ConvertMilitaryToStandard_ExcelHow = StandardTime
End Function

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

=ConvertMilitaryToStandard_ExcelHow(B1)

Where B1 is the cell containing the Military Time.

Step5: Press Enter to see the converted time in Standard Time format.

How to Convert Military Time to Standard Time in Excel vba 2.png

The above VBA code converts the Military Time to Standard Time by extracting the hour and minute components of the Military Time and then using an If-Else statement to determine the corresponding Standard Time.

3. Convert Standard Time to Military Time Using Text Formula

If you want to convert Standard time to Military time, you can use another formula based on the TEXT function. Like this:

=TEXT(C1,"HHMMSS")

Type this formula into cell D1, press Enter key on your keyboard. Drag the AutoFill Handle over to other cells to apply this formula.

convert military time to standard5

4. Video: Convert Military Time to Standard Time in Excel

This video will show you how to convert military time to standard time in Excel using both a formula and VBA code.

5. Related Functions

  • Excel TIMEVALUE Function
    The Excel TIMEVALUE function returns the decimal number of the time represented by a text string. so it will convert a time represented by a text string into an Excel time value.The syntax of the TIMEVALUE function is as below:=TIMEVALUE (time_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 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 Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

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

Insert Character or Text to Cells

This post will guide you how to insert character or text in middle of cells in Excel. How do I add text string or character to each cell of a column or range with a formula in Excel. How to add text to the beginning of all selected cells in Excel. How to add character after the first character of cells in Excel.

Assuming that you have a list of data in range B1:B5 that contain string values and you want to add one character “E” after the first character of string in Cells. You can refer to the following two methods.

1. Insert Character or Text to Cells with a Formula

To insert the character to cells in Excel, you can use a formula based on the LEFT function and the MID function. Like this:

=LEFT(B1,1) & "E" & MID(B1,2,299)

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

insert text to cells1

This formula will inert the character “E” after the first character of string in Cells. And if you want to insert the character or text string after the second or third or N position of the string in Cells, you just need to replace the number 1 in Left function and the number 2 in MID function as 2 and 3. Like below:

=LEFT(B1,2) & "E" & MID(B1,3,299)
insert text to cells2

2. Insert Character or Text to Cells with VBA

You can also use an Excel VBA Macro to insert one character or text after the first position of the text string in Cells. 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.

insert text to cells3
Sub AddCharToCells()
    Dim cel As Range
    Dim curR As Range
    Set curR = Application.Selection
    Set curR = Application.InputBox("select one Range that you want to insert one 
    character", "add character to cells", curR.Address, Type: = 8)
    For Each cel In curR
        cel.Value = VBA.Left(cel.Value, 1) & "E" & VBA.Mid(cel.Value, 2, 
        VBA.Len(cel.Value) - 1)
    Next
End Sub

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

insert text to cells4

Step6: select one Range that you want to insert one character.
insert text to cells5

Step7: lets see the result.

insert text to cells6

3. Video: Insert Character or Text to Cells

This video will demonstrate how to insert character or text in middle of cells in Excel using both formulas and VBA code.

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

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

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

Extract substring In Excel

This post will guide you how to use Excel’s MID function is a quick and easy way to extract pieces from your text. Use the Excel formula to extract a substring with MID.

Note: If you want to extract just a single character at any given time, use the “start” and end positions as your literal start/endpoints. However, if you know how many characters there will be in total (e.g., less than 10), then instead of plugging those numbers into our formula directly. They can easily become calculated values by dividing each side differently!

In the above example, we are going to extract text from a range of cells based on certain criteria. The arguments of our three-argument functions include

1) A text string

2) Starting cell number in B2 with linefeeds replaced by spaces (to make it easier for you).

3) The amount that needs to be removed before reaching C2, where we’ll find out how many characters were actually enclosed between those two points inclusive.

Each time round, these calculations happen, subtracting one point until there’s nothing left but space or a negative #.

Note: Ensure that you have the Text to Columns feature enabled in Excel before attempting this formula; otherwise, it won’t work!

=MID(A2,B2,C2-B2+1)

Extract substring In Excel1

=MID(“free excel tutorial excelhow “,1,4-1+1)

Extract substring In Excel1

=MID(A2,1,4)

Extract substring In Excel1

Extract Substrings Functions

There are three main extracting substrings functions that are Excel provides.

=MID(Text,start,chars) // extract from middle

=LEFT(Text,chars) // extract from left

=RIGHT(Text,chars) // extract from right

With This Formula Find Start And End Positions:

To handle what start and end positions are, consider the following example.

=MID(B5&C5&D5,”red”,2) // extracts “ed”

The start position of 2 is after the 3rd character, i.e., “r”. Therefore, the number of characters you wish to extract must begin from the start position.

There is no use of Start and end points in the formula because we already know how many characters we want to extract.

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

How to Count Numbers Nth Digit Equals to Specific Number in Excel

This post will guide you how to count numbers where the Nth digit equal to a specific number in Excel 2013/2016 or Excel office 365. How do I count of numbers in a given range where the Nth digit is equal to a particular number using SUMPRODUCT function and the MID function in Excel.  

General Formula:

The below general formula can help you to count the numbers where the Nth digit equal to a given number in a given range of cells in Excel. Like this:

=SUMPRODUCT(–MID(range,digit,character)=”number”)

Count Numbers Where The Nth Digit Equal To

Assuming that you have a data of list in range B2:B6, and you want to count numbers where the second digit equals to number “5”, and you can use a formula based on the SUMPRODUCT function and MID function. Like this:

=SUMPRODUCT(–(MID(B2:B6,2,1)=”5″))

Let’s See That How This Formula Works:

= MID(B2:B6,2,1)

The MID function can be used to extract the number or character from the given text string. And if you want to get the second character or number from a given range B2:B6, and you can use the above formula. And the first argument is a cell range, and the second argument specifies the start position, and the third argument indicates that the number of characters you want from the specified position of the text. This formula returns an array result like this:

{“0″;”5″;”4″;”5″;”5”}

This array results contain all the second digit from each cells in the given range B2:B6.

=MID(B2:B6,2,1)=”5″

The above array result will compare each value to “5” to generate a TRUE or FALSE result. And this formula also returned as an array result of TRUE and FALSE values. Like this:

{FALSE;TRUE;FALSE;TRUE;TRUE}

=–(MID(B2:B6,2,1)=”5″)

Then you need to use a double negative operator to convert TRUE or FALSE values to 1 and 0. It also returned an array result like:

{0;1;0;1;1}

count number n digit number7

SUMPRODUCT function sums the numbers in the above array and returns 3. 

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 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 Convert Text to Time in Excel

This post will guide you how to convert time string to time in Excel. Assuming that you have a list of text string in your worksheet, and you wish to convert these time string to standard time format, how to do it.

You need to use an formula based on the TIMEVALUE function, the LEFT function, the LEN function and the RIGHT function. Like below:

=TIMEVALUE(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2))

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

convert text to time 1

Then you need to keep these cells selected, and right click on it, select Format Cells from the context menu list and the Format Cell Dialog will open.

convert text to time 2

Switch to Number tab under Format Cells dialog box, and select Time category from the Category list, and select one time type as you need. click Ok button.

convert text to time3

convert text to time4

 

If you want to convert date and time string to a data time format in Excel, and you can use the following formula based on the DATE function, the LEFT function, the MID function, the Time Function and the RIGHT function. Like below:

=DATE(LEFT(B1,4),MID(B1,5,2),MID(B1,7,2))+TIME(MID(B1,10,2),RIGHT(B1,2),0)

Type this formula into a blank cell and press Enter key on your keyaboard.

convert text to time5

Then you need to format those selected cells to a data format as you need.

convert text to time6

convert text to time7

Related Functions

  • Excel TIMEVALUE Function
    The Excel TIMEVALUE function returns the decimal number of the time represented by a text string. so it will convert a time represented by a text string into an Excel time value.The syntax of the TIMEVALUE function is as below:=TIMEVALUE (time_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 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 TIME function
    The Excel TIME function returns a date in serial number format for a particular time (the hour, minute and second).The syntax of the TIME function is as below:= TIME(hour, minute, second)…

How to Extract Initials From a Name in Excel

This post will guide you how to get initials from a given name using a formula in Excel. How do I extract initials from names in Excel 2013/2016.

Assuming that you have a list of data in range B1:B4, in which contain name values, and you want to extract initials from these names. How to accomplish it. this post will show you two methods to get initials.

Extract Initials from a Name Using a Formula

To extract initials from a given name in Excel, you can do this using a formula based on the LEFT function, the IF function, the ISNUMBER function and the MID function. Like this:

=LEFT(B1)&IF(ISNUMBER(FIND(” “, B1)),MID(B1,FIND(” “, B1)+1,1),””)&IF(ISNUMBER(FIND(” “, B1,FIND(” “, B1)+1)),MID(B1,FIND(” “, B1,FIND(” “, B1)+1)+1,1),””)

You need to type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill Handle down to other cells.

extract initials from a name1

 

Extract Initials from a Name Using User Defined Function

You can also define a User Defined function to extract initials from a given name in Excel. 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 GetFirstCharacters(pWorkRng As Range) As String
myValue = pWorkRng.Value
myStr = VBA.Split(Trim(myValue))
For i = 0 To UBound(myStr)
    myResult = myResult & VBA.Left(myStr(i), 1) & ""
Next
GetFirstCharacters = myResult
End Function

Step5# type the following formula into a blank cell and press Enter key in your keyboard.

=GetFirstCharacters(B1)

extract initials from a name2

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 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 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 Extract Number from Text String in Excel

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using user defined function in Excel.

Assuming that you have a list of data in range B1:B5, in which contain text string values, and you want to eextract all numbers from each cell in which contain letters and numbers. In other words, you wish to remove all letters from those text strings. And this post will show you two methods to accomplish it.

Extract Number from String with Formula

If you want to extract numbers from a range of cells that contain text string values, you need to use an array formula based on the SUM function, MID function, LARGE function, INDEX function, ISNUBER function and the ROW function to extract all numbers from one cell that contain text string values. Like this:

=SUM(MID(0&B1,LARGE(INDEX(ISNUMBER(--MID(B1,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

You need to type this formula into a blank cell and press Ctrl + Shift +Enter key on your keyboard to change the current common formula as Array formula.  Then drag the AutoFill handle down to other cells.

extract number from text string1

Note: B1 is the cell that you want to extract numbers from.

Extract Number from String with User Defined Function

You can also use an VBA code to define a User defined function to accomplish the same result of extracting all numbers from text string in Excel. 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.

extract number from text string2

[vb]
Function extractNumbers(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
extractNumbers = .Replace(Txt, "")
End With
End Function
[/vb]

#5 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key. And drag the AutoFill Handle over to other cells.

=extractNumbers(B1)

extract number from text string3

Related Functions

  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • 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 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 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 LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

 

 

How to Convert mmddyy to Date in Excel

This post will guide you how to convert non-standard date formats or text to a standard date in Excel. If you have a date with mmddyy format, and you want to change it as the normal date format as mm/dd/yyyy with Excel Formula. How do I convert the mmddyyyy text to normal date format in Excel 2013/2014.

Assuming that you have a list of data in range B1:B4 which contain date values as text format. and you can use one of the following methods to convert it as normal date format.

Convert mmddyyyy Text to Date with Formula

To convert the mmddyyyy text to normal date format, you can use a formula based on the DATE function, the MID function and the RIGHT function. Just like this:

=DATE(RIGHT(B1,4),LEFT(B1,2),MID(B1,3,2))

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 to convert it.

convert mmddyyy to date1

Convert mmddyy Text to Date with Text To Column Feature

You can also convert mmddyyyy Text to a normal date format with Text to Column feature in Excel. Just do the following steps:

Step1:select the range of cells that you want to convert, such as: B1:B4.

convert mmddyyy to date2

Step2: go to Data tab in the Excel Ribbon, and click Text to Column command under Data Tools group. And the Convert Text to Columns dialog box will appear.

convert mmddyyy to date3

Step3: select Delimited option under Original Data type section in the Convert Text to Columns dialog box, and click Next button. Click Next button again.

Step4: select Date option under the Column Data format, and then choose MDY from the drop down list box of Date. Then select on destination cell to place data. Click Finish button.

convert mmddyyy to date4

Step5: you would see that the selected cells have been converted to a normal date format.

convert mmddyyy to date5

Convert mmddyy Text to Date with VBA Macro

If you want to convert mmddyyyy text to a normal date format quickly, you can also use an Excel VBA Macro to achieve the same result. 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.

export each sheet to csv2

el v

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

convert mmddyyy to date6

Sub ConvertTeoneCelltToDate()
    Set MyRange = Application.Selection
    Set MyRange = Application.InputBox("Select a Range:", "Convert mmddyyy to date", MyRange.Address, Type:=8)
   For Each oneCell In MyRange
       oneCell.Value = DateSerial(Right(oneCell.Value, 4), Left(oneCell.Value, 2), Mid(oneCell.Value, 3, 2))
       oneCell.NumberFormat = "mm/dd/yyyy"
   Next
End Sub

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

convert mmddyyy to date7

Step6: select one range which contain text data that you want to convert, such as: B1:B4. Click OK button.

convert mmddyyy to date8

Step7: Let’s see the result:

convert mmddyyy to date9

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 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 DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

 

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 Numbers from Text in Excel

This post will guide you how to remove all numbers from a text string in one cell in Excel. How do I strip all numeric characters from a given cell with a formula in Excel. How do I remove numbers from cells with text using a User Defined Function in Excel.

Remove Numbers from Text Using Formula

Assuming that you have a list of data in range B1:B4 which contain test string values. And you want to look for a formula to remove all numbers from text string in given range of cells. You can use an array formula based on the TEXTJOIN function. Like this:

=TEXTJOIN("",TRUE,IF(ISERR(MID(B1,ROW(INDIRECT("1:100")),1)+0),MID(B1,ROW(INDIRECT("1:100")),1),""))

Type this formula into a blank cell and press Ctrl + Shift + Enter keys to change the formula as array formula.

 

Remove Numbers from Text Using User Defined Function

You can also write a User Defined Function with VBA code to achieve the same result of removing all numbers from text string in the selected range of cells in Excel. Here are the 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 numbers from text1

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

=RemoveNumbersFromText(B1)

#6 drag the AutoFill handle over other cells to  remove numbers from text in one cell.

remove numbers from text2

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

How to Split Word into Different Cells in Excel

This post will guide you how to split word into different columns with a formula in Excel. How do I split word or number into separate cells with VBA Macro code in Excel 2010/1013/2016.

Split Word into Different Cells with Formula

Assuming that you have a list of data in range B1:B4, and you need to split text value or a word into separate cells in your worksheet. How to do it. You can use a formula based on the MID function and the COLUMNS function. Like this:

=MID($B1,COLUMNS($B$1:B$1),1)

Type this formula in cell C1 and press Enter key, and then drag the AutoFill Handle over to other cells in Row1.

split word into different cells1

Then you still need to drag the AutoFill Handle down to other cell to apply this formula for B2:B4.

split word into different cells2

Split Word into Different Cells with VBA Macro

You can also use an Excel VBA Macro code to achieve the same result of splitting word into different cells. Here are the 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.

split word into different cells3

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

split word into different cells4

#6 Select one Range that you want to split words. Click Ok button.

split word into different cells5

#7 Select one destination Cell that you want to place. Click Ok button.

split word into different cells6

#8 Let’see the result:

split word into different cells7

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

 

Reverse Text String in Excel

This post will guide you how to reverse text string in Excel. How do I reverse text string in one cell with an Excel User Defined Function. How to reverse a text string in one cell with a formula in Excel.

Reverse Text String with User Defined Function

You can easily write down a User Defined Function to reverse a text string in one cell. 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.

reverse text string 1

Function reverseString(str As String) As String

reverseString = StrReverse(Trim(str))

End Function

#5 Type the following formula into blank cells and then press Enter key.

=reverseString(B1)

reverse text string 2

Reverse Text String with Formula

You can also use an Excel Array formula based on the TEXTJOIN function, the MID function, the ROW function, the INDIRECT function and the LEN function to reverse text string in one cell. Like this:

=TEXTJOIN(“”,TRUE,MID(B2,LEN(B2)-ROW(INDIRECT(“1:”&LEN($B$2)))+1,1))

Type this formula into a blank cell, and then press Ctrl + Shift + Enter keys.

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