If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Removing part of the contents of a cell

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: Bedfordshire, UK
Posts: 64
Removing part of the contents of a cell

I have a spreadsheet which contains academic marks for students. They are in the form of a number followed by a letter. E.g. 6a, 4b, 7c etc.
Is there a quick way to remove the letter, so that the above examples would become simply 6, 4 and 7 ?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Kerala, India
Posts: 156
Removing part of the contents of a cell

  1. Insert a column to the right of the Marks column.
  2. Write the expression =VALUE(LEFT(D1,LEN(D1)-1)) in the new column replacing the correct left-side cell address (instead of D1) in the expression.
  3. Copy and Paste the expression all the way down to the end of the row of marks.
  4. Highlight the Range with the expression, Copy, then select Paste Special...Values

This will replace the expression with the converted numeric values alone in the column.

You may delete the column with alphanumeric values, if necessary.
__________________
www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
Learn Advanced MS-Access Programming with sample VBA Code.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: Bedfordshire, UK
Posts: 64
That's fantastic - does exactly what I want it to do! Thank you very much.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 508
If the number is always 1 digit - as implied by the formula - then another simple way to do this is by using Text to Columns > fixed width > do not import the letter portion.
__________________
Colin

RAD Excel Blog
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2011
Location: Amsterdam, NL
Posts: 6
Removing part of the contents of a cell

Or even simpler. Use the function: =LEFT(A1)

Excel Text Functions - Easy Excel Tutorial
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Kerala, India
Posts: 156
Removing part of the contents of a cell

The formula will convert the numeric part of the number (with one or more digits) with a single letter sufix.
__________________
www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
Learn Advanced MS-Access Programming with sample VBA Code.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Mar 2011
Location: Amsterdam, NL
Posts: 6
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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