Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
|
|||
|
|||
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.! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count only Letters in cells that contain both letters and numbers | Excel Worksheet Functions | |||
How to auto sum cells if letters are in cells? | Excel Worksheet Functions | |||
Capital letters in cells | Excel Programming | |||
Removing up to 3 last letters, excel | Excel Discussion (Misc queries) | |||
Removing all rows that contain letters | Excel Programming |