truncate cell contents

  • Thread starter Thread starter Marie
  • Start date Start date
M

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.
 
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)
 
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"
 
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)
 
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 :)
 
Back
Top