Removing part of the contents of a cell

kabuki · #1 (**permalink**) 05-04-11, 08:51

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 ?

apr pillai · #2 (**permalink**) 05-05-11, 02:50

Insert a column to the right of the Marks column.
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.
Copy and Paste the expression all the way down to the end of the row of marks.
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.

kabuki · #3 (**permalink**) 05-05-11, 03:37

That's fantastic - does exactly what I want it to do! Thank you very much.

Colin Legg · #4 (**permalink**) 05-06-11, 06:35

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.