Return position character first appears in a text string from the right hand side

[nightracer]

Hi all, I'm trying to return the character position of "(" in each cell working from the right hand side, so for example:

Cell A1
John Smith (GB) (22.13)
Cell A2
Adrian Blackshaw (IRE) (4.2)
Cell A3
Patrick Lourel (103.00)

So I just want the position of the final "(" in the string (before the numbers) in each case?
Some cells will have more than one "(" as above, others only one "(".
I've tried LEN, FIND etc but can't get it to work?

Many thanks
N

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[nightracer]

Thanks Fluff, I'm on 365.
I have updated my profile, thanks for the advice.

Many thanks
N

 

[KRice]

Try this:

Book1
	A	B
1	John Smith (GB) (22.13)	7
2	Adrian Blackshaw (IRE) (4.2)	5
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=MATCH("(",MID(A1,LEN(A1)-SEQUENCE(LEN(A1))+1,1),0)

Assuming you want to begin counting from the right side...or do you want to count characters from the left side and find the rightmost occurrence?

 

[Fluff]

I have updated my profile, thanks for the advice.

Thanks for that.
How about

=LEN(TEXTBEFORE(A1,"(",-1))+1

or

=LEN(TEXTAFTER(A1,"(",-1))+1

Not sure if you want to count from the left or right.

 

[nightracer]

Many thanks KRice and Fluff, it ws to count from the left, so really appreciate it, that's sorted it.

Thanks again
N

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[KRice]

Glad to help. Here's a variation on my approach using XMATCH, which has a argument to change the search direction. Using that feature to search an array of single characters representing the text string in either forward or reverse order effectively changes the counting baseline point (either from left or from right). That said, I still prefer Fluff's offering...an elegant solution. Here are both showing from-left and from-right solutions:

Book1
	A	B	C
1	John Smith (GB) (22.13)	7	17
2	John Smith (GB) (22.13)	7	17
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEN(TEXTAFTER(A1,"(",-1))+1
C1	C1	=LEN(TEXTBEFORE(A1,"(",-1))+1
B2	B2	=XMATCH("(",MID(A2,LEN(A2)-SEQUENCE(LEN(A2))+1,1),,1)
C2	C2	=XMATCH("(",MID(A2,SEQUENCE(LEN(A2)),1),,-1)

 

[nightracer]

Thank you again for all the options.

 

[Peter_SSs]

I'm wondering (guessing) if you want to know this position so that you can then split the text into columns?
If so, instead of finding the "(" position, you might be able to do the split directly? (Couple of options re the final number)

23 12 04.xlsm
	A	B	C	D
1	John Smith (GB) (22.13)	John Smith (GB)	(22.13)	22.13
2	Adrian Blackshaw (IRE) (4.2)	Adrian Blackshaw (IRE)	(4.2)	4.2
3	Patrick Lourel (103.00)	Patrick Lourel	(103.00)	103
nightracer

Cell Formulas
Range	Formula
B1:B3	B1	=TEXTBEFORE(A1," ",-1)
C1:C3	C1	=TEXTAFTER(A1," ",-1)
D1:D3	D1	=-TEXTAFTER(A1," ",-1)