Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel LOOKUP Function
  • How to get last column number in range in Excel
  • How to get last row in mixed data with blanks in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Extract data with helper column in Excel

Data Analysis Examples

  • How to Create Gantt Chart in Excel
  • Filter Data Based on Date in Excel
  • Error Bars in Excel
  • How to perform a t-Test in Excel
  • How to calculate average last N values in a table in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between
  • Excel Data validation number multiple 100
  • Excel Data validation with conditional list

Get position of 2nd 3rd and more instance of character in Excel

by

To get the position of the 2nd, 3rd, 4th, etc. instance of a specific character inside a text string, you can use the FIND and SUBSTITUTE functions.

Note: we use “~” in this case only because it rarely occurs in other text. You can use any character that you know won’t appear in the text.

Worked Example:   How to extract last two words from text string in Excel

Formula

=FIND("~",SUBSTITUTE(text,char,"~",instance))

Explanation

In the example shown, the formula in E4 is:

=FIND("~",SUBSTITUTE(B4,"x","~",D4))

How this formula works

At the core, this formula uses the fact that the SUBSTITUTE function understands “instance”, supplied as an optional forth argument called “instance_num”. This means you can use the SUBSTITUTE function to replace a specific instance of a character in a text string.  So:

SUBSTITUTE(B4,"x","~",D4)

replaces only the 2nd instance (2 comes from D4) of “x” in text in B4, with “~” character. The result looks like this:

Worked Example:   How to get first word in Excel

100×15~50

Worked Example:   Remove line breaks in a cell in Excel

Next, FIND locates the “~” inside this string and returns the position, which is 7 in this case.

Post navigation

Previous Post:

How to pad text to match equal length in Excel

Next Post:

Normalize text by removing punctuations, extra spaces and more in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to create email address with name and domain in Excel
  • Split text and numbers in Excel
  • How to Remove Leading Spaces in Excel
  • How to get top level domain (TLD) in Excel
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • Get day name from date in Excel
  • How to calculate Next working/business day in Excel
  • Sum through n months in Excel
  • Steps to create Dynamic calendar grid in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate principal for given period in Excel
  • Calculate cumulative loan interest in Excel
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles