truncate cell contents

[Marie]

How do I truncate a portion of a cell to the right of a
particular character? Example:

Jones, Bill

I want the displayed text to read:

Bill

I want to truncate everything after the comma/space. I
have tried using a combination of the MID, FIND, and RIGHT
worksheet functions but the results do not display
properly.

 

[Ken Wright]

=MID(A1,FIND(" ",A1)+1,LEN(A1))

 

[Dave R.]

If you want to transform the string "Jones, Bill" into "Bill" I think you
will have to use VB, if you want another column that just shows his first
name, this works;

=+RIGHT(A1,LEN(A1)-FIND(",",A1)-1)

 

[Marie]

oops - I meant to say I want to truncate everything BEFORE
the comma/space.

 

[Dave R.]

Whoops! Be more careful soldier.


=+LEFT(C5,LEN(C5)-FIND(",",C5))

 

[Dave R.]

And for the record you can use

=+RIGHT(C3,LEN(C3)-FIND(",",C3)-NOT(ISERROR(FIND(" ",C3))))

will work on Jones,Bill and Jones, Bill and return a 4 character "Bill"

 

[Bob Phillips]

Dave,

Your solution only works if the surname has more characters than the
forename, but Dyer, Kieron returns Dyer, K. I think you mean

=+LEFT(C5,LEN(C5)-FIND(",",C5)-1)

(you also don't need the leading + sign).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave R.]

Thanks Bob for the correction. This formula does the trick too and is the
way I probably would have done it if the OP hadn't misstated the problem in
the first place..

=+LEFT(C5,FIND(",",C5)-1)

I guess I wanted to mess with the formula I had tried to do first, so in
haste was my error- I'll have to be more careful myself!!

As for the plus sign, I guess I am used to doing it that way, and doesn't
seem to hurt anything