# Thread: Removing part of the contents of a cell

1. Registered User
Join Date
Mar 2007
Location
Bedfordshire, UK
Posts
64

## Unanswered: 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. Registered User
Join Date
Jan 2009
Location
Kerala, India
Posts
188

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

3. 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.

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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. 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

6. Registered User
Join Date
Jan 2009
Location
Kerala, India
Posts
188

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

7. Registered User
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
•