Results 1 to 7 of 7
  1. #1
    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 ?

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    159

    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.

    All responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    That's fantastic - does exactly what I want it to do! Thank you very much.

  4. #4
    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.

  5. #5
    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

  6. #6
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    159

    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.

    All responses based on Access 2003/2007

  7. #7
    Join Date
    Mar 2011
    Location
    Amsterdam, NL
    Posts
    6
    Link has been moved. Excel Text Functions - Excel Easy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •