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

 

[sandy666]

try PowerQuery

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, "\", {0, RelativePosition.FromEnd}), type text}}),
    Length = Table.TransformColumns(Extract,{{"Column1", Text.Length, Int64.Type}})
in
    Length[/SIZE]

result: 9

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Position = Table.AddColumn(Source, "BackslashPosition", each Text.PositionOf([Column1],"\",Occurrence.Last)+1)
in
    Position[/SIZE]

Result:

Column1	BackslashPosition
Monday\Tuesday\Wednesday	15​

 

[XOR LX]

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

Appreciate your help on this.

Not sure what you mean. It returns 14 for the example you give, as requested.

Regards

 

[tyija1995]

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.

How bizarre, I have here cell A2 as "Monday\Tuesday\Wednesday" and my formula is returning 14 for me. It basically substitutes the final backslash with a pipe and searches for the position, then subtracts 1 from the answer (the length of the text up to the substituted pipe) there should only return a #VALUE ! if the string doesn't contain any backslash - but then you can nest an iferror with the length of the string, only thing I can think of is if it's somehow a different backslash, can you test the unicode of the slash?

=UNICODE(**insert your backslash here**) --> it should be 92

 

[Rav_Singh]

Below is a sub set of the data;

Example
Commercial\Know-how
Commercial\Know-how\Guidance
Commercial\Know-how\Documents & Guidance\Pilot - January 2019
Commercial Lit\Know-how\Databases

Answer
10 for example 1
19 for example 2
40 for example 3
23 for example 4

Hope this helps.

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

 

[Fluff]

Whilst I misunderstood your OP, the other two formulae work for me.
I have also modified mine to work

Book1
	A	B	C	D
8	Monday\Tuesday\Wednesday	14	14	14
9	Monday\Tuesday\Wednesday\Thursday\Friday\Saturday	40	40	40
10	Commercial\Know-how	10	10	10
11	Commercial\Know-how\Guidance	19	19	19
12	Commercial\Know-how\Documents & Guidance\Pilot - January 2019	40	40	40
13	Commercial Lit\Know-how\Databases	23	23	23
Dropdowns

Cell Formulas
Range	Formula
B8	=LEN(TRIM(LEFT(SUBSTITUTE(A8,"",REPT(" ",100),LEN(A8)-LEN(SUBSTITUTE(A8,"",""))),100)))
C8	=-LOOKUP(1,-FIND("ζ",SUBSTITUTE(A8,"","ζ",{1;2;3;4;5;6;7;8;9})))-1
D8	=SEARCH("|",SUBSTITUTE(A8,CHAR(92),"|",LEN(A8)-LEN(SUBSTITUTE(A8,CHAR(92),""))))-1

 

[sandy666]

corrected

Column1	BackslashPosition
Monday\Tuesday\Wednesday	14​
Commercial\Know-how	10​
Commercial\Know-how\Guidance	19​
Commercial\Know-how\Documents & Guidance\Pilot - January 2019	40​
Commercial Lit\Know-how\Databases	23​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Position = Table.AddColumn(Source, "BackslashPosition", each Text.PositionOf([Column1],"\",Occurrence.Last))
in
    Position[/SIZE]

 

[Rav_Singh]

Thank you all. Solution found.

Oh and tyija1995 when I did rerun your formula it did work.

Really appreciate all your efforts.

A grateful Rav.

 

[Fluff]

Glad we could help & thanks for the feedback