Count the character number behind a certain character

[Rav_Singh]

I hope someone can assist with this question.

I require to understand the number of characters behind the final '\' character.

Example:

Monday\Tuesday\Wednesday

Answer in next cell = 14 (14 characters behind the final '\')

Or is a possible solution to count where the final '\' is in a cell?

This seems a workable challenge but alas I have failed to come up with a solution.

As always any help will be greatly appreciated.


Rav

 

[Fluff]

How about
=LEN(TRIM(RIGHT(SUBSTITUTE(A2,"",REPT(" ",100)),100)))
although I make the answer 9 not 14

 

[XOR LX]

Hi,

Assuming 9 is a suitable upper bound on the number of slashes in the string:

=-LOOKUP(1,-FIND("ζ",SUBSTITUTE(A1,"","ζ",{1;2;3;4;5;6;7;8;9})))-1

Regards

 

[tyija1995]

Hey how about this:

=SEARCH("|",SUBSTITUTE(A2,CHAR(92),"|",LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(92),""))))-1

Note if text contains the pipe character this will return incorrect, try change both cases of "|" to "|¬" or something else obscure.

 

[Rav_Singh]

This almost works but continues to count back from the first '\' rather than the last '\' from the string.

Appreciate your help on this.

Hi,

Assuming 9 is a suitable upper bound on the number of slashes in the string:

=-LOOKUP(1,-FIND("ζ",SUBSTITUTE(A1,"","ζ",{1;2;3;4;5;6;7;8;9})))-1

Regards

 

[Rav_Singh]

Hi Fluff,

Appreciate your input on this. When I apply this formula to a more complicated string, then the numbers veer to the high side.

 

[Rav_Singh]

Hi Tyija,

Unfortunately when I apply this formula I get the #VALUE! message.

Appreciate your efforts to assist.

Hey how about this:

=SEARCH("|",SUBSTITUTE(A2,CHAR(92),"|",LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(92),""))))-1

Note if text contains the pipe character this will return incorrect, try change "|" to "|¬" or something else obscure.

 

[tyija1995]

Hi Tyija,

Unfortunately when I apply this formula I get the #VALUE ! message.

Appreciate your efforts to assist.

Hey, thanks for the feedback - did the string you referenced (assuming it's A2) contain any backslashes (\) ?

 

[Fluff]

If the formulae you have been provided with don't work, then please supply a sample of the data where they don't work.

 

[Rav_Singh]

Yes in A2 the string contained two '\'. Was hoping to count the characters behind the final '\' in the string as some cells contain more/less '\' character.

Hey, thanks for the feedback - did the string you referenced (assuming it's A2) contain any backslashes (\) ?