Remove Final Character from Cell.

[stuart block]

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.

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.

 

[stuart block]

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

 

[AndyW]

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