Delete first two digits in column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rjtees - 17 Nov 2005 21:06 GMT

I need to delete 1 digit and then the decimal point, so that the # in the
cell is only what was after the decimal point (ie, 1.1100 to 1100). I'v got
about 2000 rows and don't have time to delete them manually.

Reply to this Message

Duke Carey - 17 Nov 2005 21:11 GMT

use an adjacent column and enter a formula such as

WORK ON A COPY OF YOUR DATA

=100*(a2-int(a2))

Copy it down for all the target values. When done, copy all these new
formulas, select the original data, and use Edit->Paste Special->Values.
Then delete your formulas

> I need to delete 1 digit and then the decimal point, so that the # in the
> cell is only what was after the decimal point (ie, 1.1100 to 1100). I'v got
> about 2000 rows and don't have time to delete them manually.

Reply to this Message

Duke Carey - 17 Nov 2005 21:19 GMT

Ignore that last answer -

use this formula instead

=RIGHT(A2,LEN(A2)-SEARCH(".",A2))*1

> use an adjacent column and enter a formula such as
>
[quoted text clipped - 9 lines]
> > cell is only what was after the decimal point (ie, 1.1100 to 1100). I'v got
> > about 2000 rows and don't have time to delete them manually.

Reply to this Message

rjtees - 17 Nov 2005 21:22 GMT

Thank you so much, worked perfectly.

Reply to this Message

Ron Rosenfeld - 17 Nov 2005 21:20 GMT

>I need to delete 1 digit and then the decimal point, so that the # in the
>cell is only what was after the decimal point (ie, 1.1100 to 1100). I'v got
>about 2000 rows and don't have time to delete them manually.

Excel stores 1.1100 as 1.11.

If you always want to see as a positive integer the four places after the
decimal point, then use the formula:

=TRUNC(MOD(A1,1)*10^4)

If you want something else, you will need to be more specific.

--ron

Reply to this Message