How to find the position of second space character in a name?

[Gautham A]

Hello,
Let's say we have the below in cell A1 and A2
In cell A1-> "Alba and Nikita"
In cell A2-> "James and Benjamin"

I want to find the position of second space, from cell A1 I want the output to be 9 and cell B1 the output should be 10. Is there any formula for this? Thanks in advance

 

[Habtest]

Hi, this should do:

Book1
	A	B
1	Alba and Nikita	9
2	James and Benjamin	10
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=FIND(" ",A1,FIND(" ",A1)+1)

 

[Toadstool]

Here's another approach, the only benefit of which is SUBSTITUTE lets you specify which instance to replace, hence find.

GauthamA.xlsx
	A	B	C
1	Name	Habtest	Toadstool
2	Alba and Nikita	9	9
3	James and Benjamin	10	10
4	Jim Jones	#VALUE!	#VALUE!
5	His Lordship Reginald Andrew Fortescue Hythe III Esq	13	13
6	John Jones IV	11	11
7	Frank Harry Arnold Smith	12	12
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=FIND(" ",A2,FIND(" ",A2)+1)
C2:C7	C2	=FIND("{{{}}}",SUBSTITUTE(A2," ","{{{}}}",2))

 

[Gautham A]

Thank you both. You saved me a lot of time

 

[Toadstool]

Thank you both. You saved me a lot of time

You're welcome.