remove last three characters of cell

M

mira

Thanks, Access Expert! Your formula works really great!

Now, I am trying to use the same formula to remove the last three characters
which contain a comma, a space, and one letter (it varies, and a wildcard
doesn't seem to work) (e.g. ", J"), how do I modify it to remove the last
three characters. I've been playing around with it, but don't know how to
specify the letter as a wildcard, if that's even possible.

I want to to go from Doe, John, W --> Doe, John

Thanks!
 
R

Ray A

Easier way
Highlight column >Ctrl + H>Find , leave replace blank. This will erase all
of the comma's in the column
 
R

Ray A

The only change is the removal of the comma. Find the comma and leave the
replace blank.
 
M

mira

I can't do that b/c I don't want to remove all the commas. I want to remove
the comma, the space, and the letter at the end of the string
 
S

SVC

Try: =LEFT(A1,LEN(a1)-3)

This will remove the last three "characters" in the string (i.e., comma,
space and any final letter)
 
B

B. R.Ramachandran

Hi,

=LEFT(A1,LEN(A1)-3) should work (unless I am missing something from your
original posting). Modify A1 appropriately.

Regards,
B.R. Ramachandran
 
D

Dave Peterson

How about:
=left(a1,len(a1)-3)

or maybe:
=if(mid(a1,len(a1)-2,2)=", ",left(a1,len(a1)-3),a1)
just to be careful.
 
M

mira

Thanks, Dave!

Both work, but the 2nd one is better for me so I can just drag it down the
column.

THANKS!!!! This has made my job easier!
 
Top