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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

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

"mira" wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

This will not work for my case since the letter is always different.

"Ray A" wrote:

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

"mira" wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

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

"mira" wrote:

This will not work for my case since the letter is always different.

"Ray A" wrote:

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

"mira" wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

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

"Ray A" wrote:

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

"mira" wrote:

This will not work for my case since the letter is always different.

"Ray A" wrote:

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

"mira" wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

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

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

"mira" wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

Hi,

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

Regards,
B.R. Ramachandran
"mira" wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

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.

mira wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

--

Dave Peterson

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!

"Dave Peterson" wrote:

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.

mira wrote:

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!

"AccessExpert" wrote:

Mira,

You can use a combination of the Left and Right functions. Insert a blank
column to the right of column you wish to change. In my example I will
assume the data is in column A. Change the formula as necessary.

In the empty cell enter the formula:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

This formula is checking to see if the last character in cell A1 is infact a
comma. If it is the formula will return all of the characters in cell A1
except the last one. If it is not a comma then will just return the entire
contents of cell A1.

Copy this formula down to all the rows necessary. Then Copy the entire
column and paste the values over the original column (A). Make sure you use
the Paste Special and choose the Values Option. Now you should be able to
delete Column B.


"mira" wrote:

I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this?

--

Dave Peterson