Formula help

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
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
AB
1TEST1 TEST2 TEST3 TEST4TEST4
2NOTEST1 NOTEST2 NOTEST3 NOTEST4NOTEST4
3JFIE AOIEJFJ AOSFE AFEJIOSFE AFEJI
Sheet1
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

vlady

Active Member
Joined
Jan 26, 2012
Messages
413
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
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,725
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1TEST1 TEST2 TEST3 TEST4TEST4=RIGHT(A1,23-6-6-6+1)
2JFIE AOIEJFJ AOSFE AFEJIOSFE AFEJI=RIGHT(A3,24-5-5-5+1)
3Here we go.e go.=RIGHT(A4,5)
4
Sheet
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,725
Office Version
  1. 365
Platform
  1. Windows
Here is one way to get 2nd word.
Excel Workbook
AB
1TEST1 TEST2 TEST3 TEST4TEST2
2NOTEST1 NOTEST2 NOTEST3 NOTEST4NOTEST2
3JFIE AOIEJFJ AOSFE AFEJIAOIEJFJ
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top