Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing last character of cell
I am trying to remove a comma which happens to be the last character in the
cell. Does anyone know how to do this? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
hi,
if all the data has the same number of characters you can use =left(a1, 6) where a1 is the cell the data is in and 6 is the number of characters you want to keep. (change it to the number of characters in your data.) if your data is different lengths, you can use dataTextToColumnsdelimitedcomma but be careful. text to columns will delete data to the right of the column you are parsing. if you do have data to the right, copy the column you want to parse to a blank sheet. regards FSt1 "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? |
#4
|
|||
|
|||
If its the only comma, use Edit 'Replace , Replace With Nothing
Replace All If not, =SUBSTITUTE(A1,RIGHT(A1,1),"") Paste Special Values to lose the formula's Regards, Alan. "mira" wrote in message ... I am trying to remove a comma which happens to be the last character in the cell. Does anyone know how to do this? |
#5
|
|||
|
|||
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 (e.g. , J), how do I modify it to remove the last part. I've been playing around with it, but don't know how to specify the letter. 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell color index comparison | New Users to Excel | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
How to know that character type in one cell of xls is more that s. | Excel Discussion (Misc queries) | |||
Fill a cell with a character | New Users to Excel | |||
Removing a space within a cell | Excel Worksheet Functions |