How do I seperate the last word from a cell?

[Prashant]

Hi
Can anybody please help me by explaining the following:
I want to separate the last word from a cell which consists of two, three or
more words into another cell. For example -
a cell consist of "qwer tyui asdf", I would like to make it into two cells
i.e. "qwer tyui" and "asdf".
Kindly explain the whole process.
Thx

 

[RagDyeR]

Here's a thread from a couple of days ago that answers your question
exactly.

http://tinyurl.com/3nvtu
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi
Can anybody please help me by explaining the following:
I want to separate the last word from a cell which consists of two, three or
more words into another cell. For example -
a cell consist of "qwer tyui asdf", I would like to make it into two cells
i.e. "qwer tyui" and "asdf".
Kindly explain the whole process.
Thx

 

[Don]

Hi RD,

Always trying to pick up new ideas here. Visited that
link, copied and pasted your formula as you explained with
some names in Col A and formula starting in B1...I've
checked the format...all are formatted General but all I
get is "#Value" in Col B.....what am I doing wrong?

Don

 

[RagDyeR]

Did you read the *entire* thread?

There were two reasons mentioned for getting that error, and two solutions!

Did you try them both?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi RD,

Always trying to pick up new ideas here. Visited that
link, copied and pasted your formula as you explained with
some names in Col A and formula starting in B1...I've
checked the format...all are formatted General but all I
get is "#Value" in Col B.....what am I doing wrong?

Don

 

[JohnI in Brisbane]

Don,

I got the same problem. The web page has a line break, which you need to
remove and add a space between the double quotes.

regards,

JohnI

 

[Don]

Thanks RD and John,

Made the correction and it's working fine now.

Don

 

[JohnI in Brisbane]

RagDyeR,

Good formula there.

My breakdown:-

Working from right to left on formula,

1. Remove all spaces
SUBSTITUTE(A1," ","")

2. Length without spaces
LEN(SUBSTITUTE(A1," ",""))

3. Length with spaces
LEN(A1)

4. Number of spaces
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

5. Substitute last space with ^^^
SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

6. Position of last space
SEARCH("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

7. Length with spaces (again)
LEN(A1)

8. Length with spaces - Position of last space
LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))

9. Get rightmost characters (after last space).
RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1,"
","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Also had a look at the "tinyurl" site. - interesting as well.

regards,

JohnI
===================================

 

[Ragdyer]

I'm glad you appreciate the formula, but I do believe the credit belongs to
Leo Heuser who first helped someone with it back 3 or 4 years ago.

It's definitely one that you would label a "saver" ... which is where I dug
it out of ... my "saver" file.