How to separate last combine characters which is separated by space

hi nur2544, try:
=MID(A2,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

copy down

Try pasting this into cell B2:

Formula:

Please Login or Register  to view this content.

Hi,benishiryo
it works fine, but the problem is that when it gets blank cell or others value then it returns #VALUE!, how to remove it.
i have tried like below but results goes to "SS 1B" "CLASS 2B" "CLASS 2AC"

Please Login or Register  to view this content.

Hello nur2544

You can turn off the #VALUE! in options. Otherwise try this formula in cell B2 and copy down:

Formula:

Please Login or Register  to view this content.

hi mc84excel
It works fine , although i get puzzle to see the big formula !. i always love small formula. as my requirement is fulfillment so i am going to use this..
how ever i have another post would you please look, here is the link
http://www.excelforum.com/excel-form...cell-b1-b.html

Originally Posted by nur2544

hi mc84excel
i get puzzle to see the big formula !

The secret to creating an impressively big formula is to work out what you want to achieve, break it down into small individual tasks and then work out the formulas for each task. Once you have all the small formulas working correctly, you copy and paste these little formulas into one big formula.


I'll explain how I solved your question:

First off, I had the formula I provided in my first post

Formula:

Please Login or Register  to view this content.

This formula will return the last word in a cell taken from the last " " in the string.

However you didn't want the #VALUE! message to appear. (This was because the cell had no " " spaces in it so the formula I supplied couldn't calculate). So I had to determine if the target cell had no " "s in it. I wrote this formula to return the number of " "s appearing in the target cell:

Formula:

Please Login or Register  to view this content.

As I expected, this formula returns an error if there are no " " in the target cell. I then altered this formula to read:

Formula:

Please Login or Register  to view this content.

(By using ISERR, the cell displays TRUE if the calculation will result in an error). So we now had a quick way of testing if the previous formula would show #VALUE!


Now all I had to do was to combine both of these formulas into one single formula by means of an IF statement:


IF (enter the ISERR formula above to determine if cell has no " ") =TRUE then return "". If not then (enter the formula to find last word in cell).
Thus:

Formula:

Please Login or Register  to view this content.

I hope this helps to explain how the big formula was worked out.



(Actually I could have used: =IF(ISERR(original formula),"",(original formula)) However I wanted to give you a smaller formula so I used the FIND " " formula for the ISERR check instead).

Hi

Another simple one!

Formula:

Please Login or Register  to view this content.

Originally Posted by Kevin UK

Hi

Another simple one!

Formula:

Please Login or Register  to view this content.

Much smaller formula. I like it! (Just don't use it if the cell contains more than 100 spaces, right?)

@ mc84excel

Why not! Put 200 space's between each word, then use the formula.

Originally Posted by mc84excel

Much smaller formula. I like it! (Just don't use it if the cell contains more than 100 spaces, right?)