how to delete ampersand at end of field

**opus88** · 03-22-2010, 03:53 PM

I am working with an address list. The first field has names in such format as:
JONES THOMAS L & MARY A &
SMITH LAWRENCE W JR
THOMPSON JAMES & CATHERINE

I need a formula to delete that final ampersand when it appears, yet leave the other ampersands intact. I have been searching but cannot find a solution. I'm not an expert but have used a few other formulas and macros to accomplish various tasks.

A solution would be greatly appreciated, as I run into this type of list quite often, and the manual deletion is killing my hand, not to mention my time!

**Palmetto** · 03-22-2010, 03:59 PM

Assuming the last ampersand is always the last character, then this will delete the ampersand the the space before it.

=TRIM(MID(A1,1,LEN(A1)-1))

**mdbct** · 03-22-2010, 04:03 PM

If it is the last character in the field you can use:

Please Login or Register  to view this content.

then do a copy - paste special... values over the original column.

**opus88** · 03-22-2010, 04:06 PM

Thanks to both of you for jumping in! These possible solutions will leave the middle ampersand intact? And if it's not an ampersand at the end of the field, it won't touch whatever other character is at the end?

**mdbct** · 03-22-2010, 04:10 PM

Mine will only lop off the Ampersand when it is the right most character, leaving all other ampersands alone. Palmetto's will lop off the last character regardless of the character.

**opus88** · 03-22-2010, 04:15 PM

Silly me - I guess I could have tested it before I asked that, couldn't I? Sorry!

BIG, BIG, BIG thanks for helping me with this. You have saved me many hours of tedium. AWESOME!