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

Data Analysis

  • Number and Text Filters Examples in Excel
  • How to create Checklist in Excel
  • How To Create Frequency Distribution in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Use Data Form to input, edit and delete records in Excel

References

  • Extract data with helper column in Excel
  • How to get relative row numbers in a range in Excel
  • How to get last row in text data in Excel
  • How to get address of last cell in range in Excel
  • How to get relative column numbers in a range in Excel

Data Validations

  • Excel Data validation date in next 30 days
  • Excel Data validation don’t exceed total
  • Excel Data validation must not contain
  • Excel Data validation must begin with
  • Prevent invalid data entering in specific cells

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 Remove Leading Spaces 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:   Replace one character with another in Excel

100×15~50

Worked Example:   Split dimensions into three parts in Excel

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

Post navigation

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

Next Post:

Create One-dimensional and Two-dimensional Array

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

  • Complete List of Excel Logical Functions, References and Examples
  • IF, AND, OR and NOT Functions Examples in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function

Date Time

  • Display Days in month in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Steps to create Dynamic calendar grid in Excel
  • Custom weekday abbreviation in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Map text to numbers in Excel
  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel

General

  • Spell Check in Excel
  • Delete Blank Rows at Once in Excel
  • How to increase by percentage in Excel
  • How to calculate percent sold in Excel
  • Print Excel Sheet In Landscape Or Portrait
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning