Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Junior Member
 
Posts: 27
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count only Letters in cells that contain both letters and numbers TommyB Excel Worksheet Functions 2 August 8th 09 12:29 AM
How to auto sum cells if letters are in cells? [email protected] Excel Worksheet Functions 2 February 7th 09 09:55 PM
Capital letters in cells [email protected] Excel Programming 2 November 28th 07 11:50 AM
Removing up to 3 last letters, excel [email protected] Excel Discussion (Misc queries) 1 June 28th 07 09:26 PM
Removing all rows that contain letters fred Excel Programming 2 February 15th 05 04:22 PM


All times are GMT +1. The time now is 04:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"