Removing letters from cells
 

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.
Anyone out there who can help?

Removing letters from cells
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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[/quote]

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

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