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?
0
mira (9)
7/27/2005 5:39:07 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
703 Views

Similar Articles

[PageSpeed] 22

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?
0
7/27/2005 6:03:01 PM
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 
data>TextToColumns>delimited>comma

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?
0
FSt1 (238)
7/27/2005 6:05:02 PM
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" <mira@discussions.microsoft.com> wrote in message 
news:D3BB2EDF-F6F0-4A2C-97E5-502FB88E4139@microsoft.com...
>I am trying to remove a comma which happens to be the last character in the
> cell.  Does anyone know how to do this? 


0
alan111 (581)
7/27/2005 6:05:18 PM
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?
0
mira (9)
7/27/2005 6:46:24 PM
Reply:

Similar Artilces:

sum of cells
Hi, In Cell AB10 I need a formula to sum the following cells inthe same line. The cells are: D, F, H, J, L, N, P, R, T, V, X, Z Is there a way to do this other than using: =d10+f10+h10........+z10 (sum of every other cell!!) =SUMPRODUCT(--(MOD(COLUMN(D2:Z2),2)=0),D2:Z2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Khalil handal" <khhandal@yahoo.com> wrote in message news:up25YVaXIHA.280@TK2MSFTNGP04.phx.gbl... > Hi, > In Cell AB10 I need a formula to sum the following cells inthe same line. > The cells ar...

How do I put drawing objects behind text/cells in a spreadsheet?
Using a background will not work because I am dealing with mutliple objects which need regular moving and changing. since objects sit on top of the sheet, i am not sure you can do that unless the object has a transparent setting. >-----Original Message----- >Using a background will not work because I am dealing with mutliple objects >which need regular moving and changing. >. > ...

Find difference between 2 cells if critera in a 3rd cell is met
How would I find the difference between two cells depending on what is in a third cell? For example, in column G1 I want to show the difference E1 and whichever cell in column F contains the number 1. I was thinking something along the lines of using SUMPRODUCT to find the row with the #1 and then somehow subtracting G1 from the number column E that match the row where the number 1 was found. This is VERY confusing! At least, to me it is. There's probably a simple solution but I don't understand. You want the formula in cell G1? This is the really confusing part: ...

Cell Value + open application
Hi all, the following is what I am trying to accomplish: I have a spread sheet that I use to track incoming and outgoing documents. What I would like to be able to do is have cell in which I enter a name into open up and outlook template I have saved in the same directory (.oft file). Also be able to a pull information from other cells to populate the subject of the template. Thanks in advance for any assistance with this ...

cell reads as label but returns a value
How do you have a cell read on the spread as a lable (using the label in a drop-down list) and when selected, the cell will return a constant value? The cell might read: "Red Widget" but it would return a value of say: 257. Got any ideas on this one.... Thanks... Spydor -- spydor ------------------------------------------------------------------------ spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28438 View this thread: http://www.excelforum.com/showthread.php?threadid=480383 Hi spydor, Is this what you mean? http://hanjohn.customer....

VLOOKUP returning LAST match
Hi guys, Lets take this scenario: Brian 20 Michael 30 Ivan 40 Brian 30 Ivan 60 Michael 70 I want to apply a vlookup on this list but return the last value i.e. Brian 30, Ivan 60 & Micahel 70 I tried the -1 instead of the 0 in the last field of the formula but this still did not work correctly. Any suggestions ..??? Thanks, Brian =vlookup() will return the value from the first match. But this seems to work ok for me: =LOOKUP(2,1/(A1:A10="michael"),B1:B10) Brian Ferris wrote: > > Hi guys, > > Lets take this scenario: > > Brian 20 &...

Last activity date/time for a case
Can anybody provide me with details of how I can either display in the case form or the SQL equivalent so I can produce a report of the date, time and title of the latest activity that was created on a Case. We have a business requirement to supply clients not only with details of outstanding cases, but when the last activity took place and the details of that activity There are probably several approachs you could take to accomplish your objective, write some custom code based on the sdk that updates fields on the case form when activities are created, etc. One of the easiest methods...

5.5 to 2k3 upgrade
Hey, I've seen this posted a hundred times, but none of the previous resolutions helped. Have 5.5 running on 2000 server. Just added a Win2k3 server and Exchange 2k3. Chose 1st option for migration. All steps went smooth, no failures, 1 warning about wins. Got to the last part where i am using ADC to attach to the new server and I'm getting c103aa1d errors on ports 379 and 390 (unable to contact exchange server) and c103aa1a for port 389 (enter a valid server name). So far I've reinstalled ADC, changed ports on 5.5 between 379, 389 and 390, changed service accounts, forced replicat...

Removing addresses
When you want to insert an address into a Word document you have the option of using the arrow next to the Insert Address icon to get a drop-down list of addresses previously inserted. Is there a way to clear addresses that appear in that drop-down box? Thank you in advance for your help. Highlight it and press DELETE. To completely remove the cache locate your nk2 file (hidden file) and rename it to .old -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "...

combo box in each cell of a column
I would like to have all the cell of a column (ie column B) associate to a combobox Each selection should be independent from the other, but the list o selection values should be the same. Can someone help me? Thanks & cia -- Robert ----------------------------------------------------------------------- Roberts's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1576 View this thread: http://www.excelforum.com/showthread.php?threadid=27276 Hi use 'Data - Validation - List' and use the same list source for all drop down. Also see: http://www.contexture...

How to set focus or select next cell
I have five fields of inputs (say B5,C5,D5,E5,F5), after putting the data in cell F5, when I hit the enter key is there a way that the next focus cell is B6 for the next input, if so how? In other words after data is inputted in cell F5 the focus jumps to the next line at cell B6 ... thanks ahead for the solution. -dave Hi Dave Select the range B5:F62 before you start typing and it will behave like that for quite a while. HTH. Best wishes Harald "dave wagner" <dwagner@lancorpnet.com> skrev i melding news:IHbaf.7447$D13.133@newssvr11.news.prodigy.com... > I have...

How to remove an external data source
Hi, Please guide me how to remove an external data source link in pivot as i need to make a new link (I am using excel 2007) Thanks Dinesh Hi - In your worksheet - go to the Edit Menu - then select 'Links' way at the bottom and go from there. "Dinesh" wrote: > Hi, > > Please guide me how to remove an external data source link in pivot as i > need to make a new link (I am using excel 2007) > > Thanks > Dinesh ...

Tab in a cell #2
Hey all! You know how in excel if you tab, you go to the next cell to the right? How can I tab in a cell, to make an indent? I also have a weird problem with this particular cell (maybe it is due to me spacing instead of tabbing...) In a cell, I have 3 paragraphs of written information. The last three lines however just keep going to the right, instead of wrapping, like I have this cell formatted to do. Any thoughts on how to resolve either of these problems? -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/me...

Formatting just visible cells
Is there a way to apply formatting to just visible cells on a worksheet. I have two columns hidden and when I apply font color to my worksheet, the hidden columns are applied with the font color as well. Is there a way to prevent this? Suggestions or help would be appreciated. Select your region, then choose Edit/Goto. Click Special, then select the Visible cells only radio button. Click OK. Apply your format. In article <140e801c3f7ce$ba5656d0$a501280a@phx.gbl>, "bagia" <bagia@ureach.com> wrote: > Is there a way to apply formatting to just visible cells ...

Format cells to Auto Enter
I have 2 columns that I only have to input 2 characters in each column. ie 3d, ad. Is there a way to format the cells so that when the 2nd character is entered it will automatically shift to the next cell without having to hit the "ENTER" or "TAB" keys TIA Dave No Excel needs to know when you have finished editing a cell. Enter, Tab or Arrow Gord Dibben MS Excel MVP On Wed, 25 Jun 2008 16:21:00 -0700, Dave <Dave@discussions.microsoft.com> wrote: >I have 2 columns that I only have to input 2 characters in each column. ie >3d, ad. >Is there a way...

Filling formulas with specific cell references remaining the same
I need to fill a formula in a range of cells downward, specifically the formula IF(OR(O2=U1,P2=U1,Q2=U1),2-L2,0), with the U1 cell references remaining the same, continuing to refer to U1, while the other cell references (O2, P2, Q2, and L2) change and fill normally. Conversely, I need to be able to fill the formula right with U2 being the only reference that changes. Is this possible to do? If so, how? Doing this manually would take several hours. I am using Excel 2007. A suitable topic to type into Excel help is "relative & absolute addressing" The dollar sign i...

Remove posted credit to business customer view
How about I simply ask how to post a credit to a customer, then remove it? Somehow a credit was applied to a customer, now I need to figure out how to remove it. Is it possible? So.. what I need to know is.. 1. How to add a credit (without posting a credit to the bank account). 2. How to remove that same credit (again, without posting to the bank account). Thanks for any assistance you can provide. Sincerely, SE Lake ...

Unable to enter "S" or words starting with "S" in cells
Am working with Excel 2000 (Version 9.0.6926 SP-3) When entering words starting with "s" or just "s", the entry fails and brings up one of several menus. The menus vary depending upon how many keystrokes I have made before realizing the text entry failed. "S" is teh only letter I have found where this problem occurs. If I double click on the cell then enter the word starting wtih "s", then I can enter the text. This is cumbersome and very time consuming. Please advise Do you have the same problem with any other programs? It sounds to me like a...

counting cells #4
Hi this may seem a daft question but i'm having real problems trying to count cells... i want to count cells in a range say a17 to a40 inclusive - i want to know the total number of cells in a range and for it to include all types eg empty cells,text numbers and formulas sam Hi try: =ROWS(range)*COLUMNS(range) -- Regards Frank Kabel Frankfurt, Germany "dave h" <usenet@nospamsoremovesupersports.plus.com> schrieb im Newsbeitrag news:OfxV20OoEHA.1800@TK2MSFTNGP15.phx.gbl... > Hi > > this may seem a daft question but i'm having real problems trying to c...

macro triggered by changes to cell only works if i run it twice
Hi, While developing a form with some automated elements I have come into a problem about triggering a macro when a user selects an option from a drop down cell (the drop down list is created through validation). I have previously been running the macro whenever ANY cell changes in the workbook, which was working fine. However, this started interfering with another macro I have added doing something else, so i have been trying to get my orginal macro to only run when the specific cell (F12) changes by a user selecting a dropdown option. I have tried several ways, with the ...

How to select a cell to the left of the right aligned cell with th
Hello everybody! How to select a cell to the left of the right aligned cell with the =, when anyone enters a formula? The cell to the left of the right aligned cell with the =, does not have left and right borbers and cannot be selected by the mouse. Best regards, Dima +79035093892 I'm just guessing here... But I think you're talking about the extended cell that comes up whenever you're typing in a formula. It's the same cell as the one you're typing in, except Excel made it look bigger so it'd be easier to see what you're typing in. "Dima" wrote: ...

Fomula to copy every third cell in col
I have a list of business addresses and phone numbers in 1 column. Ideally I would like Col A the be name. Col B address and c phone number. Here is the format I have right now. Please help. Ultimate Staffing 3435 W Cheyenne Ave, North Las Vegas, NV (702) 647-0477 - 2.8 mi SE Allied Forces 1401 N Decatur Blvd # 21, Las Vegas, NV (702) 647-6668 - 4.3 mi S Labor Express 1401 N Decatur Blvd # 21, Las Vegas, NV (702) 647-6668 - 4.3 mi S Workers 1401 N Decatur Blvd, Las Vegas, NV (702) 647-6668 - 4.3 mi S Flexible Staffing 800 N Rainbow Blvd, Las Vegas, NV (702) 948-5030 - 4.8 mi S On...

Need to add cell comments in unlocked cell on protected worksheet
Using Excel 2002, I have a worksheet that I have "protected" so that only a certain block of cells can be selected/edited. Today, I have a new requirement to be able to add/edit/delete cell comments on those cells that remain unlocked. But the options to do so on the cell context menu disappear when the worksheet is protected, but reappear when the worksheet is unprotected. When you protect the worksheet, scroll down and check "edit objects". This will allow comments to be added/removed/changed--along with other objects (pictures/shapes/etc). dan400man wrote: >...

Formatting Cell Fractions... HELP
Using Excel I formatted cells using fractions as quarters. how can I convert the 2/4 to 1/2 now? all of the numbers of my cells now show either 1/4, 2/4 or 3/4. I wanted to be able to change the 2/4 for 1/2 without having to format each one. Thank you Before I formatted the number showed 101.5, 102.8 - after formatting 101 2/4, 102 3/4. I was trying to find a way to change all the 2/4 for 1/2. Please don't multi-post You have an answer in your other thread -- Kind regards, Niek Otten Microsoft MVP - Excel "Twicebest" <Twicebest@discussions.microsoft.com> wrote...

How do I block all e-mail that contain special characters?
As a brute-force method of getting rid of spam, how can I set up Outlook Express to delete all e-mail messages that contain ANY special characters? I don't need the whole host of foreign characters, symbols, bracket/curly brackets, and the like. When I've attempted to set up a rule to exclude a character, it doesn't seem to work... thanks, da cub This site may help: http://slashdot.org/comments.pl?sid=24362&cid=2641548 --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all ma...