Office & Productivity Software forum

General discussion

function to display partial number

by lopezsal / June 22, 2005 5:19 AM 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
You are posting a reply to: function to display partial number
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: function to display partial number
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: function
by Kees Bakker / June 22, 2005 6:30 AM 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,
by MarkFlax Forum moderator / June 23, 2005 5:14 AM 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 ...
by Kees Bakker / June 23, 2005 7:38 AM PDT

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

Kees

Collapse -
Oops!
by MarkFlax Forum moderator / June 23, 2005 8:58 AM PDT

Methinks you're right!

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

Mark

Collapse -
Elegant solution.
by Kees Bakker / June 23, 2005 6:02 PM PDT
In reply to: Oops!

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
by lopezsal / June 24, 2005 1:42 AM PDT
In reply to: Elegant solution.

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

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

CNET FORUMS TOP DISCUSSION

Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?