Got the formula, Need an Explanation

[Willmonbo]

I needed to find the second instance of the "/" charachter within a text string, and searched the web and found this and it works, since what i need was the rest of the text string to the right, but I can't figure out how you arrice at this construction and why this works, i'd greatly appreciate if one can give me a brief explanation.

following is the formula;


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

 

[Teethless mama]

In the main menu click Formulas > Select Evaluate Formula > click Evaluate button and watch excel evaluate formula step by step

 

[circledchicken]

I needed to find the second instance of the "/" charachter within a text string, and searched the web and found this and it works, since what i need was the rest of the text string to the right, but I can't figure out how you arrice at this construction and why this works, i'd greatly appreciate if one can give me a brief explanation.

following is the formula;


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

You will probably figure this out after following Teethless mama's suggestion, but if your always looking for the second instance of "/" (rather than the last), then this shorter version would work too:

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

 

[T. Valko]

I needed to find the second instance of the "/" charachter within a text string, and searched the web and found this and it works, since what i need was the rest of the text string to the right, but I can't figure out how you arrice at this construction and why this works, i'd greatly appreciate if one can give me a brief explanation.

following is the formula;


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

This will do the same thing and is a bit shorter and eaier to understand. Assumes there will always be 2 "slashes" in the string:

A1: This/That/The Other

=MID(A2,FIND("^^",SUBSTITUTE(A2,"/","^^",2))+1,50)

Within the string This/That/The Other, replace the 2nd instance of "/" with "^^", find the character location number of "^^", add 1 to that location number and extract the next 50 characters starting from that point.

 

[c_m_s_jr]

I needed to find the second instance of the "/" charachter within a text string, and searched the web and found this and it works, since what i need was the rest of the text string to the right, but I can't figure out how you arrice at this construction and why this works, i'd greatly appreciate if one can give me a brief explanation.

following is the formula;


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

RIGHT this function will pull X number of characters starting at the right end of a text string
LEN this function will determine how many characters (the length) of a text string
FIND this function will find the position of the character or characters
SUBSTITUTE this function will substitute a character or string of characters with another. It can replace ALL or a specific instance

The trick of the formula is replacing the correct "/" (1st, second, 3rd, etc) with a unique character set that will not be found anywhere else in your text string ("^^"). To determine which "/" instance to replace the
LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) part of the formula is called. This will calculate the total length of the string. It then replaces all instance of "/" and calculates the length. The total length is then subtracted from the revised length without the "/". This value represents which instance of use in the first SUBSTITUTE formula:

SUBSTITUTE(A1,"/","^^",...

The first SUBSTITUTE formula then replaces the correct instance of "/" with "^^". The FIND function is then used to determine the position of "^^" within the string. This position is subtracted from the overall length (LEN(A1)-FIND("^^",...) to figure out how many places starting from the right need to be pulled.

Its important to note that the formula you found will pull the last set of characters of a string if there are 2 "/" or 3 or 4 etc since it is designed to find the position of the last "/". So...

test/1234/this is what I want = This is what I want
test/1234/not this/but this = But This

 

[Willmonbo]

thank you all, your help is much appreciated.

 

[T. Valko]

thank you all, your help is much appreciated.

You're welcome. Thanks for the feedback!