# Find nth instance of a character in a string

1. ## Find nth instance of a character in a string

Mod. edit: BrisbaneBob - please don't post q's in another member's thread (no matter how old it is) - post moved to dedicated thread.

This formula was offered as a method of finding the nth instance of "/" in a string. I have used it successfully. I have run it through Formula Evaluate to see how it works but I still can't figure it out. Can someone be kind enough to explain to me how it works?

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

Thanks and regards

2. ## Re: Find nth instance of a character in a string

The formula substitutes the nth instance of "/" with ^^ and then uses ^^ to determine starting point of string to return.

n is determined by LEN of string less LEN of string having had all instances of "/" removed

Consider:

A1 = "apples/pears/oranges"
Len of A1 is 20

Determine n
n = 20-18
18 being the LEN of A1 if you take out the "/"

We use n [2] as final argument within SUBSTITUTE call to determine which instance of "/" we wish to replace with "^^"
(when this argument is excluded [optional] all instances are replaced)

SUBSTITUTE(A1,"/","^^",2)

At which point A1 equates to: "apples/pears^^oranges"

To return "oranges" we return right n characters where n is determined by Len of string [20] less position of ^^ within revised string.

There are alternatives to the above but it's a fairly common approach.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1