Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

function to display partial number

Jun 22, 2005 5:19AM PDT

I need a function that would change 68.75 to just 8.75, 99.01 to 9.01, etc only in a report. These are real numbers so I need their value to remain but in the display and print in my report the leading number needs to be replaced with a letter which I have. Just need something to make the leading number disappear. Any suggestions? Thanks.

Discussion is locked

- Collapse -
Re: function
Jun 22, 2005 6:30AM PDT

Assuming all numbers are between 10.00 and 99.99 it's something like number minus 10*integer(number/10). Easy to check.
Translate it to whatever language you use.

Kees

- Collapse -
Or if you Kees' suggestion doesn't work for you,
Jun 23, 2005 5:14AM PDT

try this;

A1 holds 67.12
B1 holds; =RIGHT(A1,4) - Converts 67.12 to a string, then extracts the 4th number from the right, leaving the 3 right most numbers
C1 holds; ="A"&TEXT(A1,"0.00") gives A7.12

The TEXT function does this;
"A" is added to whatever the result is.
& adds the "A"
"0.00" is the "Category" the number is to be in as decided in Cell > Format > Number (tab) > any category other than General; eg, a currency would be "$0.00".

Works with larger numbers as well, eg;

A2 holds 100.01
B2 holds; =RIGHT(A2,4) - becomes 0.01
C2 holds; ="FRED"&TEXT(B2,"0.00") - becomes Fred0.01

Any good for you?

Mark

- Collapse -
'Report' let me think of Access ...
Jun 23, 2005 7:38AM PDT

more than Excel. That's why I answered as I did.

Kees

- Collapse -
Oops!
Jun 23, 2005 8:58AM PDT

Methinks you're right!

Darn, I thought mine was an elegant solution as well.

Mark

- Collapse -
Elegant solution.
Jun 23, 2005 6:02PM PDT

Mark,

Of course it was. It's easy to do this character handling in Access also. The syntax is

Right(format(number,"0000.00"),4)

And that, by the way, works in Excel also. No need to use intermediate cells, although it helps to use them to develop and debug a formula like this. But I wouldn't use them in the final solution.

If the numbers are >=100 and you want 198.67 displayed as 98.67, while displaying 23.31 as 3.31 you need an IF somewhere to discriminate between the last 4 and last 5 characters. And in my original numerical solution you need an if to discriminate between dividing/multiplying by 10 or by 100.

Whatever way you do it, it's not too difficult.

Regards,


Kees

- Collapse -
loosing my leading number
Jun 24, 2005 1:42AM PDT

That function looks like exactly what I wanted. I'll have to test all combinations. Thank you so much for your help. Regards Sally