How to get the position of Last Occurrence of a character or string in a cell

In the previous post, we have talked that how to get the position of the last occurrence of a specific value in a single range or column. And this post will guide you how to get the position of the last occurrence of a character or text string in a Cell using below two ways in excel.

Get the position of Last Occurrence of a character Using Excel Formula

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. For example: Assuming that there is a text string in Cell B1, and you can use the following formula to get the last occurrence of the character “-” in Cell B1:

=LOOKUP(2,1/(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)="-"),ROW(INDIRECT("1:"&LEN(B1))))

Let’s see how the above formula works:

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

Get the position Using Excel Formula 1

The ROW function will return the row number based on the row references returned by the INDIRECT function. so it will return the below array:

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

 

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

Get the position Using Excel Formula 1

I have talked the above MID function in the previous post to split text string into an array in a cell. So the above formula returns an array like as below:

{"e";"-";"x";"-";"c";"-";"e";"-";"l"}

 

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

Get the position Using Excel Formula 1

x
How to Select Every Other Row in Excel

The above formula will check each element in array returned by the above MID function if it is equal to the dash character (“-“). If so, returns TRUE, otherwise, returns FALSE. As it is an array formula, so the above formula should also return an array like as below:

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

 

=1/(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)=”-“)

Get the position Using Excel Formula 1

The above formula use 1 to divide each element in the array returned by the MID function. If the value of element in array is TRUE, then returns 1, otherwise, returns a #DIV/0! Error. Just like the below:

{#DIV/0!;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!}

 

=LOOKUP(2,1/(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)=”-“),ROW(INDIRECT(“1:”&LEN(B1))))

Get the position Using Excel Formula 1

The LOOKUP function searches for the value of 2 in the first array returned by “1/MID()” formula, you should know that the lookup function can’t find the value of 2 in the first array, so it uses the largest value in the first array that is less than or equal to 2.then returns a value from the same position in the second array returned by the second ROW function.

So the result returned by the above LOOKUP function is the position of the last occurrence of the dash character in Cell B1. It is 8.

 

You can also use another formula to achieve the same result as follows:

=SEARCH("#",SUBSTITUTE(B1,"-","#",LEN(B1)-LEN(SUBSTITUTE(B1,"-",""))))

Let’s see how the above formula works:

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

Get the position using Excel Formula6

The SUBSTITUTE function will replace all dash characters (“-“) of Cell B1 with empty string.

 

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

Get the position using Excel Formula7

The above formula returns the numbers of dash character in the Cell B1.

 

=SUBSTITUTE(B1,”-“,”#”,LEN(B1)-LEN(SUBSTITUTE(B1,”-“,””)))

Get the position using Excel Formula8

The first SUBSTITUTE function will replace the last occurrence of the dash character with the hash character in Cell B1.

 

=SEARCH(“#”,SUBSTITUTE(B1,”-“,”#”,LEN(B1)-LEN(SUBSTITUTE(B1,”-“,””))))

Get the position using Excel Formula8

The SEARCH function searches the hash character in a text string returned by the first SUBSTITUTE function, and then returns the position of the hash character. It should be the last occurrence of the dash character in Cell B1.

Get the position of Last Occurrence of a character using user defined Function

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

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

Get the position using Excel vba1

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

3# click “Insert” ->”Module” to create a new module named as: getPosition

Get the position using Excel vba2Get the position using Excel vba2

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

Get the position using Excel vba2

Function getPosition1(strVal As String, strChar As String) As Long
    getPosition1 = InStrRev(strVal, strChar)
End Function

 

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

= getPosition1(B1,"-")

Get the position using Excel vba4


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 the nth Occurrence of a Character in a Cell
    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.
  • 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.…

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 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 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 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 LOOKUP function
    The Excel LOOKUP function will search a value in a vector or array.The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
  • 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 SEARCH function
    The Excel SEARCH function returns the number of the starting location of a sub-string in a text string. The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])….
Related Posts

How to Remove All Asterisk Characters from Cells in Excel
How to Remove All Asterisk Characters7

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

How to Convert Date Format from Dot MM.DD.YYYY to Slash MM/DD/YYYY in Excel
Convert Date Format from Dot to Slash 6

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

How to Remove Quotes for Text or Strings in Excel
How to Remove Quotes 8

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

How to Remove Specific Character from Text Cell in Excel
remove specific character from text string6

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. Remove Specific Character Using Find and Replace function Remove ...

How to Count Comma Separated Value in One Cell in Excel
count value separated by comma2

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

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

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 Remove the First/Last Word from Text string in Cell
remove first last word in cell2

This post will guide you how to remove the first and the last word from a text string in cells in Excel. How do I use a formula to remove first and last word of a text string in Excel. ...

How to Remove Line Breaks (Carriage Returns) in Excel
remove line breaks9

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 Convert DD-MM-YYYY text string or date to a standard date format in Excel
convert ddmmyyy test to date format1

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 Video: Convert DD-MM-YYYYY to Standard Date Format Convert Text ...

How to convert Column Letter to Number in Excel
convert column letter to number8

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

Sidebar