Removing letters from cells

[Quilp]

I have a column where the cells contain V11, C139, D27 etc. I need t
strip the letters out so that I can apply the MAX formula to the entir
column. The entries are also entered as text so I will need to remov
the apostrophe. I have only just discovered the text/value toggle so
unwittingly, I have been entering text instead of "value" (number).
have formatted the column as "number" and have tried highlighting th
cells and toggling to "AUTO" but the damned apostrophe remains.
Anyone out there who can help

 

[Claus Busch]

Hi,

Am Tue, 17 Sep 2013 09:46:47 +0100 schrieb Quilp:

I have a column where the cells contain V11, C139, D27 etc. I need to
strip the letters out so that I can apply the MAX formula to the entire
column. The entries are also entered as text so I will need to remove
the apostrophe. I have only just discovered the text/value toggle so,
unwittingly, I have been entering text instead of "value" (number). I
have formatted the column as "number" and have tried highlighting the
cells and toggling to "AUTO" but the damned apostrophe remains.

if your values are in column B then insert an empty column right from B
and enter following formula:
=--RIGHT(B1,COUNT(RIGHT(B1,COLUMN(1:1))*1))
and enter the array formula with CTRL+Shift+Enter and copy down. Select
this column and copy it, then insert => Paste special => Paste values
and delete column B.
If formatting doesn't work click on column header, format the column
with your wished format and choose Data => TextToColumns => Fixed Width
=> Finish


Regards
Claus B.

 

[Quilp]

if your values are in column B then insert an empty column right from B
and enter following formula:
=--RIGHT(B1,COUNT(RIGHT(B1,COLUMN(1:1))*1))
and enter the array formula with CTRL+Shift+Enter and copy down. Select
this column and copy it, then insert => Paste special => Paste values
and delete column B.
If formatting doesn't work click on column header, format the column
with your wished format and choose Data => TextToColumns => Fixed Width
=> Finish


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I am sorry Claus but I still can't get this to work - no doubt due to m
lack of experience of worksheets.
However, I have now done it the hard way - I have deleted both the lette
and the apostrophe from each of the 400 or so cells, one by one, using th
delete key. It took me ages but now I have been able to use the Ma
formula.

I have saved a copy of my worksheet as an "Exercise Sheet" which I wil
use to practice on in order to learn more about spreadsheets and
hopefully, to enable me to follow forum members suggestions in th
future.