Remove Final Character from Cell.

Hi All,

I am trying to remove the final character from a cell with a find and replace.

the cell contents are like the following example.

07APR07A

needing to be 07APR07

But other cells in the list are already in the format of

07-Apr-07
07APR07*

I have managed to remove the * by using the tide ~ character. So I don't 
know if there is a similar character to remove the last character only.

But am stuck with the last character. Can someone please help.
0
3/10/2008 10:16:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
491 Views

Similar Articles

[PageSpeed] 47

=LEFT(A1,LEN(A1)-1)

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"stuart block" <stuartblock@discussions.microsoft.com> wrote in message news:6CB29265-F183-4C44-8FBE-E1F98F1BFEAD@microsoft.com...
| Hi All,
|
| I am trying to remove the final character from a cell with a find and replace.
|
| the cell contents are like the following example.
|
| 07APR07A
|
| needing to be 07APR07
|
| But other cells in the list are already in the format of
|
| 07-Apr-07
| 07APR07*
|
| I have managed to remove the * by using the tide ~ character. So I don't
| know if there is a similar character to remove the last character only.
|
| But am stuck with the last character. Can someone please help. 


0
nicolaus (2022)
3/10/2008 10:43:35 AM
Thanks Niek,

I already know this calculation would work, but I am updating upto 12 
columns, with 50,000 cells in each.

So adding extra 600,000 calculations, would A. slow the machine down and B. 
I would have to review each cell, as some of them are already formatted 
correct.

I just need to remove the last "A" from some of the cells.

Stuart

"Niek Otten" wrote:

> =LEFT(A1,LEN(A1)-1)
> 
> -- 
> Kind regards,
> 
> Niek Otten
> Microsoft MVP - Excel
> 
> "stuart block" <stuartblock@discussions.microsoft.com> wrote in message news:6CB29265-F183-4C44-8FBE-E1F98F1BFEAD@microsoft.com...
> | Hi All,
> |
> | I am trying to remove the final character from a cell with a find and replace.
> |
> | the cell contents are like the following example.
> |
> | 07APR07A
> |
> | needing to be 07APR07
> |
> | But other cells in the list are already in the format of
> |
> | 07-Apr-07
> | 07APR07*
> |
> | I have managed to remove the * by using the tide ~ character. So I don't
> | know if there is a similar character to remove the last character only.
> |
> | But am stuck with the last character. Can someone please help. 
> 
> 
> 
0
3/10/2008 11:05:00 AM
"stuart block" <stuartblock@discussions.microsoft.com> wrote in message 
news:FCD7294C-E03F-4572-88D1-9F611A1CA22D@microsoft.com...
> Thanks Niek,
>
> I already know this calculation would work, but I am updating upto 12
> columns, with 50,000 cells in each.
>
> So adding extra 600,000 calculations, would A. slow the machine down and 
> B.
> I would have to review each cell, as some of them are already formatted
> correct.
>
> I just need to remove the last "A" from some of the cells.

If it is a one-off thing your best bet would be to use a small piece of VBA 
code that cycles through each cell, checks to see if the last character is 
the letter A and truncates the contents of the cell by 1 character.
If you are comfortable with using VBA the code is simple.

The code below will cycle through each cell in the selected area and 
truncate the cells that end in "A"

Just go into VBA editor, paste the code below then go to the sheet you want 
checked, select the area you want and run the code. It is not very elegant 
but should do the job for a one-off.
It works on Excel 2003, should work on others

SAVE THE SHEET FIRST! Preferably under a different name in case of problems.

Sub TruncateCells()

Dim rng As Range

For Each rng In Selection
    If Right(rng, 1) = "A" Then
        rng = Left(rng, Len(rng) - 1)
    End If
Next

End Sub


AndyW 


0
3/10/2008 12:07:43 PM
Reply:

Similar Artilces:

How to I set the margins for a cell in Excel?
Though I have word wrap on, the words are running over into the next cell. This could have something to do with the fact that I have hard-returns in the cell. I wanted to ensure the margins weren't set to -.5 (or something like that) which would allow this kind of short run-over. Are you sure you've toggled wraptext? Format|cells|alignment tab I've never seen text bleed over to adjacent cells with this toggled correctly. blinko wrote: > > Though I have word wrap on, the words are running over into the next cell. > This could have something to do with the fact tha...

Selected cells grow and data entry impossible EXT is dissabled
How do I dissable this option? I select a cell and move the cursor which selects other cells. I also cannot enter data anywhere or select any options in the tool bar after slecting a cell. Extended Selection Option is dissabled and verified through the status bar. Sometimes the mouse buttons stick. Gently bang all the buttons (including the wheel button if you have it) to see if that helps. David McRitchie has some notes about this kind thing at: http://www.mvps.org/dmcritchie/excel/ghosting.txt Allanhart42 wrote: > > How do I dissable this option? I select a cell and move the cu...

Cell changes color
I have a range that is all colored yellow. For some reason, one of the cells changes to white when the user enters a number in it. Anyone know why and how to prevent? Thanks. What you are describing can occur if conditional formatting has been applied to the cell. -- Gary's Student "JG Scott" wrote: > I have a range that is all colored yellow. For some reason, one of the > cells changes to white when the user enters a number in it. Anyone > know why and how to prevent? > > Thanks. > > "JG Scott" <jgscott3@bellsouth.net> wrote i...

Shading Blank Cells Between Values?
Hello, I have a program that I would like to keep track of pending leave for our employees. There is a sheet for every employee. I enter leave start dates and duration into these sheets. There is a master sheet with all employees names that looks like a calendar. A function places "Start" in the cell of the start date of leave, and "End" in the cell of the end date of leave. I can use conditional formatting to shade the cells that have "Start" and "End" in them, but I can't figure out how to shade the blank cells between the two. I...

Dropping leading characters
In my picklist, I have some leading periods to show hierarchy. i.e. Heading ......Sub heading ...........Sub sub heading I want to be able to display an alert which will drop the leading periods and just display the relevant text. i.e. "You have selected Sub heading" as opposed to "You have selected .....Sub heading". Are there any trim functions that will allow me to do this? I'm learning CRM and the language conventions as I go, so if anyone can point me to a site which gives standard functions, that would be great. Cheers ...

cell format changing when when using search and replace.
I have a macro that places a large number of pictures on a spreadsheet. I would like the pictures to show as "placeholders" to speed up viewing/editing. The only problem is that this feature (under Tools/Options/View) does not work. All the images are being display normally. I have tried this in Excel 2000/2003/XP with the same result. This feature works great in MS Word. Please help. To see what I'm talking about. Go to Tools/Options/View and check the "Show Placeholders" option. Now insert a picture or clipart. The image will be displayed normally instead of just a b...

Excel Cell Formatting, multiple lines
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to force a line wrap within a cell in Excel, similar to the way Shift^Return forces a line wrap in a table cell in Word? Control+Command+Return as well as Control+Option+Return. Have a look in Excel Help for others: Keyboard shortcuts used in Excel. HTH |:>) Bob Jones [MVP] Office:Mac On 5/22/10 1:51 PM, in article 59bb8db7.-1@webcrossing.JaKIaxP2ac0, "IHS@officeformac.com" <IHS@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (S...

Inserting characters into excel
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I need to insert a squared sign into excel however there is no such character in the symbols section. <br><br>any advice would be much appreciated As part of a formula, use ^2. <br><br>For display purposes, you will need to use formatting menu. Enter x2 into a cell, highlight the &quot;2&quot; in the cell, click on format menu, go to Cells. Then select Fonts and enable Superscript option. That will display the 2 as a superscript and leave the rest of the cell normal. <br><br>-Katie ...

How can I get the active cell in a worksheet to be highlighted in.
When working on a very large spreadsheet, I find it difficult to identify the active cell quickly and it would be helpful it it could be highlighted in a different color than all the rest. Is there any way to do this? Hi not quite what you have asked for but see: http://www.cpearson.com/excel/RowLiner.htm Note: This kind of event procedure will disbable the UNDO functionality -- Regards Frank Kabel Frankfurt, Germany "DizzyD" <DizzyD@discussions.microsoft.com> schrieb im Newsbeitrag news:DA26FEF3-1092-429E-BDB8-8F5319F31B2B@microsoft.com... > When working on a very la...

Cell subtraction
In E17 I placed =If(isblank(A17),"===",B17-C17) In A17 I have the date 4/4/05 In B17 is the negative number -30.33 In C17 is the number 0.00 In E17 I get 30.33 instead of -30.33 How can I fix this to get -30.33 in E17 Also if A17 is blank/empty and B17 is empty and C17 is 500 then I get #VALUE! in E17 instead of === gudway@charter.net Your formula works correctly when values are keyed into A17 and B17 and C17. What formulas do you have in those cells? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence wit...

need a cell to highlight
can this be done? when i put somthing in "D3" say a letter "Y" i would like for "F3" to highlight say a color say " blue" can that be done?? jo -- Message posted from http://www.ExcelForum.com Format>conditional formatting, formula is =EXACT($D$3,UPPER($D$3) click format and select pattern, click OK twice -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "jladika >" <<jladika.17jkuj@excelforum-nospam.com> wrote in message news:jladika.17jkuj@excelforum-nospam.com... >...

Count consecutive cells if negative
Hi all, Is there a way to do this in excel? I have a column of numbers and I would like to count what the max number of consecutive negatives are. For instance, if I have a series of numbers (1,3,5,-2,-5,3,-7)), I would like it to return 2 as the max number of consecutive negatives. Thanks in advance! --- Message posted from http://www.ExcelForum.com/ There may be a more elegant way, but here is one that worked for me: Assuming the data resides in Column A (I will use A2:A10 for illustration), I copy the following formula down next to the data in Column B from B2 to B10: =IF(A2<0,...

pethow do i insert a Yes/No function into a cell in Excel?
How do i insert a Yes/No function into a cell in Excel? petethomson Wrote: > How do i insert a Yes/No function into a cell in Excel? Hi pete Try an If function eg IF(A1=10, "Yes","No"), this is saying if A1 = 10 return the value Yes, if not return No -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=467552 ...

How do I copy data from one cell to another.
Hello. I'm having trouble creating a macro that will let me cut postcodes from one cell and paste into another cell. I would appreciate if anybody can help me. What I have, is a large excel spreadsheet with a couple of thousand names and addresses. The address fields start in column B through to G. Each line of the address should be in a separate cell within each row, but the postcodes have all ended up in the same cell as the last line of the address. I need to cut the postcodes out of these cells and paste them into column H which is empty. The addresses are different lengths, leavi...

Finding cells with leading characters
My spreadsheet looks like this: A 1 000000 2 182068 3 198506 4 200081 5 368010 6 580905 7 680001 8 686850 9 690060 10 700012 11 701068 12 802054 13 920211 14 968902 Using EDIT/FIND, when I enter '68' in the "Find What:" box, Excel finds A2, A5, A7, A8, A11 and A14 because their values contain '68'. What do I enter in the "Find What:" box to find the cells whose values BEGIN with '68'? (In this example, it would find ...

auto data source, removing null
I have a chart that is automatically generating a chart based on the data imported into the spreadsheet, however, the number of rows of data is not the same, so when the chart generates based on a selection that has more rows selected than there is data for, it creates a very weird looking chart. ..Columns("A:B").Select .Charts.Add .ActiveChart.ChartType = xlColumnClustered .ActiveChart.SetSourceData Source:=Sheets("Bookings Made").Range ("A1:B24"), PlotBy:=xlColumns (NB: I am using with statments for ease of use) as you can ...

How do I change color of active cell in Excel
I would like the active cell (cell I am currently working in) to be a different color. Example: I am working in cell AD58 I would like for that cell to be blue, then as I move through the sheet, the current active cell would be blue. right click sheet tab>view code>insert this>SAVE to use for only one cell change Set MyRng = Target.EntireRow to Set MyRng = Target ===== 'McCurdy.Here is something inspired by Don Guillett. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyRng As Range Set MyRng = Target.EntireRow Application.EnableEvents = False On Error GoT...

will installing remove all emails?
Will installing Office XP from 2000 pro get rid of all my emails in Outlook? Thanx Max "Maxinator" <Maxinator@discussions.microsoft.com> wrote in message news:512EDC43-8B4F-4FE3-8080-C9912CFBE5E4@microsoft.com... > Will installing Office XP from 2000 pro get rid of all my emails in > Outlook? > Thanx > Max No it shouldn't do, but as in any scenario, backup just in case. Gordon's advice is very sound. When in doubt, always back up your data. Here's great information on how to do it: http://www.howto-outlook.com/howto/backupandrestore.htm -- ...

Attaching Macros to cells
Does anyone know If you can attach a VBA macro to a cell so that it runs when you enter out of the cell ...

Can I conditionally lock/unlock some cells
Hi I am making a journal on a spread sheet whereby each row is a successive date. I would like to make it so that the user can ONLY change the current row (date = TODAY). I figured out how to use conditional formatting to make the current date row a different color but I would like to go further and make it so the user can only change the current line. Can this be done? How? Thanks in advance Hi, you can use 'Data - Validation' and check if the date in one cell is equal to TODAY() Frank Another way is to use a macro that unprotects the worksheet, locks all the cells, unlo...

Formatting Cells Containing Date Fields That Are Text
Someone here at work imported into Excel a column of text data that looks like mm/dd/yyyy form but need to be mmddyyyy. I thought I could simply highlight the columns, click on Format/Cells/Custom and create mmddyyyy. I do this but the data doesn't change UNTIL I highlight each cell and press F2 or double-click in each cell or create a formula using Right, Mid, etc. When I press F2 or double-click, I notice that an X and a check mark both appear to the right of the Row/ Column box. There's got to be an easier way to do this besides 'onesie-twosie' but we can't figure it...

auto fill data into a cell from a lookup table
Does anyone know how to set up a cell formula so that if the data in a cell (such as a county name) is entered, an amount is placed in the cell in the next column? Hi use VLOOKUP to do this say you have your list of countries and amounts on sheet 2 in the range A1:B100 and then on sheet 1 you want to type a country name in cell A1 and have the amount appear in B1 the formula for B1 would be =VLOOKUP(A1,Sheet2!$A$2:$B$100,2,0) which says, look at the value in A1, find it in the first column of the table in Sheet2 and return the associated value from the second column of this table whe...

limit number of characters in a cell
I am creating an application form to be used electronically. I want a particular cell to be free text, so users can write what they like (ie their name), but I want to limit the number of characters they can use. Can this be done? I have put text wrap on the cell, but it still allows me to enter more information than fits into the box. I want it to stop users entering info when the visible cell is full. Rob, Teesside, UK. "abfabrob" wrote: > I am creating an application form to be used electronically. I want a > particular cell to be free text, so users can write wh...

removing incorrect Contacts?
I've got two lots of contacts appearing in the drop-down address box. One is correct, the other isn't. I can't for the life of me remember how to get rid of the spurious one? ta! What version of Outlook "Gordon" <gordon@gbpcomputing.co.uk.invalid> wrote in message news:OeVOEU2wFHA.1028@TK2MSFTNGP12.phx.gbl... > I've got two lots of contacts appearing in the drop-down address box. One > is > correct, the other isn't. I can't for the life of me remember how to get > rid > of the spurious one? > > ta! > > "Noel All...

removing unapproved disclaimers
List, We are dealing with a situation where users are adding inapproiate signatures and disclaimer type messages to their email. I have found a ton of resources on adding disclaimers but I am wondering if anyone out there has found a method to remove them? Thanks Curt On Thu, 15 Dec 2005 10:38:25 -0500, "Curt Shaffer" <curt@chilitech.net> wrote: >List, > >We are dealing with a situation where users are adding inapproiate >signatures and disclaimer type messages to their email. I have found a ton >of resources on adding disclaimers but I am wondering if any...