Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Everything about Charts in Excel
  • Excel Pie Chart
  • How to create Gauge Chart in Excel
  • Understanding Pivot Tables in Excel
  • Get column name from index in Excel Table

References

  • How to get last row in text data in Excel
  • How to use Excel ROW Function
  • Offset in Excel
  • How to use Excel OFFSET function
  • Create hyperlink with VLOOKUP in Excel

Data Validations

  • Excel Data validation whole percentage only
  • Excel Data validation date in specific year
  • Data validation must not exist in list
  • How To Create Drop-down List in Excel
  • Excel Data validation number multiple 100

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:   Manipulating text strings using Left, Mid, Right, Len, Substitute 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 split text with delimiter in Excel

100×15~50

Worked Example:   How to count specific characters in a range 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

Logical Functions

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • Get day from date in Excel
  • Get month from date in Excel
  • How to get same date next year or previous year in Excel
  • How to get Weekdays, Working days between Two Dates in Excel
  • Find Last Day of the Month in Excel

Grouping

  • Map text to numbers in Excel
  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel

General

  • How to calculate percent of goal in Excel
  • How to generate random date between two dates in Excel
  • Check if range contains a value not in another range in Excel
  • Print Excel Sheet In Landscape Or Portrait
  • How to fill cell ranges with random text values in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning