Formula help

[Donai]

Hi,

I am trying to isolate string after the last space for each test, the formula I have used works for the first 2 tests but not the third, why is this?

Excel Workbook
	A	B
1	TEST1 TEST2 TEST3 TEST4	TEST4
2	NOTEST1 NOTEST2 NOTEST3 NOTEST4	NOTEST4
3	JFIE AOIEJFJ AOSFE AFEJI	OSFE AFEJI
Sheet1

 

[vlady]

try this one.

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
if still i think there's a hidden char between those two words

AOSFE AFEJI

 

[Donai]

Why not this?

=RIGHT(A1,FIND(" ",A1))

 

[AhoyNC]

The issue is your formula is not finding the last space. Each find in your formulas is just finding the position of the first space.

In the sheet below column C shows what your formula is actual calculating. Notice every find is returning the same number. Also if you look at your formula in B! on post 1 it actual returns a space before test4.

Your formula in post 4 will not work for the same reason.

See this link on how to get the last word in a string.
https://www.youtube.com/watch?v=CkkW04bdHeM

Excel Workbook
	A	B	C
1	TEST1 TEST2 TEST3 TEST4	TEST4	=RIGHT(A1,23-6-6-6+1)
2	JFIE AOIEJFJ AOSFE AFEJI	OSFE AFEJI	=RIGHT(A3,24-5-5-5+1)
3	Here we go.	e go.	=RIGHT(A4,5)
4
Sheet

 

[Donai]

Thanks.

How do isolate "TEST2" in A1

 

[AhoyNC]

Here is one way to get 2nd word.

Excel Workbook
	A	B
1	TEST1 TEST2 TEST3 TEST4	TEST2
2	NOTEST1 NOTEST2 NOTEST3 NOTEST4	NOTEST2
3	JFIE AOIEJFJ AOSFE AFEJI	AOIEJFJ
Sheet