separating last word in a cell fro 500 cells in column...urgent

R

roger901

I have seen how you can break out last word in a cell, but what if yo
have 500 cells in a column you need to do...for instance breaking ou
last name in 500 names like:

Mr. & Mrs. John R. Smith
Tom Jones
Tom Balal
Mr. Frank Smith

I know nothing about functions so please be as specific as you can
 
E

Earl Kiosterud

Roger,

I know of no function that will search from the right, so one will have to
be written. If you're willing to paste a user-defined function into a
module, we can write it for you (and you'd just paste it from the post), you
could then use it in a cell. SOmething like this, which you'd put in a
separate column, and copy down:

=LastName(A2)

I haven't written it yet, because some folks just glaze over and disappear
when faced with a macro. It means going into the Visual Basic for
Applications environment. Not complicated. Post back.
 
R

RagDyeR

With your names in column A, starting in A1, enter this formula in B1:

=RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1,"
","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Now, select B1 with the formula in it.
In the lower right corner of the selected cell, you'll see a small black
square.
Hover you cursor over this square until the cursor changes from a fat white
cross to a skinny black cross.
Then *double click*.

This will copy the formula in B1 down column B, as far as there is data in
column A.

Now, while all these cells are *still* selected, right click in the
selection, and choose "Copy".
Right click *again* in the selection, and choose "PasteSpecial".
Then click in "Values", then <OK>.

You have just extracted the last word from all the cells in column A.
You then removed the formula from column B, leaving just the text behind,
which you can now copy to anywhere.
--

HTH,

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


I have seen how you can break out last word in a cell, but what if you
have 500 cells in a column you need to do...for instance breaking out
last name in 500 names like:

Mr. & Mrs. John R. Smith
Tom Jones
Tom Balal
Mr. Frank Smith

I know nothing about functions so please be as specific as you can.
 
R

roger901

RagDyeR,

I did what you said, and I get #VALUE! all the way down clum
B, but when I copy and paste, it just stays #VALUE!


What do I do now, or what did I do wrong
 
R

RagDyer

I'm guessing that you didn't do anything wrong.
You're getting the error because you *don't* have any spaces in column A.
By that , I mean "normal" spaces, CHAR(32).

You probably have "non-breaking" spaces, CHAR(160), because I'll bet that
you imported the data.

Text To Columns could take care of this problem and eliminate the CHAR(160),
but *not* with your configuration of multiple mixed names in a cell.

I would think that you can probably replace the "bad" spaces with "good"
ones, using "Edit & Replace".

This now assumes that the spaces *are* CHAR(160).

With the *same* extraction formula residing in column B, select all of
column A.
Then do <Edit> <Replace>,
In the "Find What" box, enter:
<Alt> <0160>
Where you *must* use the numbers from the num keypad, *not* the numbers
under the function keys.
You will *not* see anything displayed in this box.
In the "Replace With" box, just enter a normal <space>, then "ReplaceAll".

If the spaces where truly "non-breaking", CHAR(160) spaces, this should now
give you all your last names in column B, where you can then eliminate the
formulas.

Let me know if it works.
--

HTH,

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


RagDyeR,

I did what you said, and I get #VALUE! all the way down clumn
B, but when I copy and paste, it just stays #VALUE!


What do I do now, or what did I do wrong?
 
D

Debra Dalgleish

When you pasted the formula into your worksheet, the space in the
formula may have been lost. It should be:

=RIGHT(A1,LEN(A1)-SEARCH("^^^",
SUBSTITUTE(A1," ","^^^",LEN(A1)-
LEN(SUBSTITUTE(A1," ","")))))

with a space between the quotation marks in each Substitute formula
 
R

RagDyer

Never thought of that Deb!
That would be too simple.<bg>
--


Regards,

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

When you pasted the formula into your worksheet, the space in the
formula may have been lost. It should be:

=RIGHT(A1,LEN(A1)-SEARCH("^^^",
SUBSTITUTE(A1," ","^^^",LEN(A1)-
LEN(SUBSTITUTE(A1," ","")))))

with a space between the quotation marks in each Substitute formula
 
R

roger901

RagDyeR,

I got it to work, now here is another question. What could I d
different to subtract that last name from column 1, now that I have th
last names in column 2?

Thanks a bunch
 
R

RagDyer

Enter this formula in C1:

=LEFT(A1,LEN(A1)-(LEN(B1)+1))

And copy down like you did the first formula, and then replace the formula
if you wish, as you did with the other.
--

HTH,

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


RagDyeR,

I got it to work, now here is another question. What could I do
different to subtract that last name from column 1, now that I have the
last names in column 2?

Thanks a bunch.
 
R

RagDyer

BTW,
Does <"I got it to work">
Mean there were CHAR(160) spaces,
OR,
Were there spaces missing from the pasted formula, as Debra suggested?
Just curious.
--


Regards,

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

Enter this formula in C1:

=LEFT(A1,LEN(A1)-(LEN(B1)+1))

And copy down like you did the first formula, and then replace the formula
if you wish, as you did with the other.
--

HTH,

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


RagDyeR,

I got it to work, now here is another question. What could I do
different to subtract that last name from column 1, now that I have the
last names in column 2?

Thanks a bunch.
 
R

roger901

RagDyer,

Yes, there was a space missing just like she said. I appreciate all th
help...I think this will came in very handy for the work I do
 
R

RagDyeR

I guess that shows a difference in mindset.

When the sound of hoofbeats is heard,
Debra thinks horses,
I think zebras.
--

Regards,

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

RagDyer,

Yes, there was a space missing just like she said. I appreciate all the
help...I think this will came in very handy for the work I do.
 
D

Dave Peterson

Severely off-topic:

Which reminds me of a joke I heard 40 something years ago (on the tv game show
"Truth or Consequences").

What's a zebra (an audience member asked Bob Barker, IIRC).
BB: I don't know.

The largest size you can buy.
 
R

RagDyer

You're not referring to Debra ... are you Dave?<vbg>
--


Regards,

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

Severely off-topic:

Which reminds me of a joke I heard 40 something years ago (on the tv game
show
"Truth or Consequences").

What's a zebra (an audience member asked Bob Barker, IIRC).
BB: I don't know.

The largest size you can buy.
 
D

Debra Dalgleish

Thank you for your support! <g>

Dave said:
Severely off-topic:

Which reminds me of a joke I heard 40 something years ago (on the tv game show
"Truth or Consequences").

What's a zebra (an audience member asked Bob Barker, IIRC).
BB: I don't know.

The largest size you can buy.
Click to expand...
 
D

Dave Peterson

I would never take the chance of offending Deb with this kind of humour!

(well, if I was sure I could get away with it, maybe...)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2002: How to lookup the last number and text in a column ? 3
make cell equal a word if a data validation cell has a word in it 1
Value in the last cell of a moving column 4
Sumproduct using part of a Cell 1
HOW? Find and display the last cell in a column with a value > 0 10
Pasting a block of Word info into a single Excel cell 3
Merging Groups of Cells in a Column 1
Selecting last word in a string 3

Top