Hi,
I know with formulas you can reduce characters in a cell.
What I want to do is just show the last digit in a cell with custom formatting because I use 4 digit values and when I create a chart with 2 sets of values take into account the single digit values as well.
So rather than doing that, I thought if I can reduce the cell number to last digit only with custom formatting, maybe it could work.
Any thoughts?
Formatting does not change the underlying value of a cell, it is just the way XL shows you your data.
If you need to work with only the last digit you'll have to extract it with a formula
Perhaps post a sample sheet to show us what you want to achieve ( see yellow banner for how-to)
BTW are you still using XL2003? If not please adapt your profile as answers are often tailored to the OP's XL version
Hi,
Thanks for replying. I updated my profile since I use excel 2016. Number formatting is what I need since I don't want the value to change.
The idea is to show the last digit from the amount colum as a data lable in chart since my amount values are 4 digits and have 2 or 3 dicimals I don't want the full value to appear as data lable.
Where's the chart???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Pepe has already told you that formatting ONLY changes how the cell contents look to the human eye. 619.3454 will REMAIN 619.3454. If your custom formatting IS actually possible... it will LOOK like 4, but that's all.
What use is an ambiguous data label anyway?? You have two 4s, 5s and 0s.
This is very confusing.
Getting the last digit is, of course, fairly straightforward:
=RIGHT(B2)
will do that. But I don't see what that achieves.
Hi, sorry for not attaching the chart before.
I revised the sheet so you can understand better.
One of the two price values will have a negative value so to plot it on the same level is too complicated for me.
So I decided to have separate data series for positive and negative and use two different color formate to visually identify them. And for the purpose of this price sheet I only want to visualize the last digit of the price.
So 619.3454 as 4 is exactly what I need.
Thank you
Last edited by vonmunchy; 01-11-2022 at 05:16 AM.
?? Is this what you wanted?
Hi,
Not as value from cell. Because I want to formate the lable for each of the series separately.
It needs the last digit of the value of it's series because my main sheet will have 1000s of prices so to manually color code it would be too time consuming
Attached is a screenshot of what im trying to achive
If there is a number format code that will do this, it would be documented here: https://support.microsoft.com/en-us/...7-9c9354dd99f5 I do not see any way to display only the rightmost digit of a number, however.
Since you are already extracting the rightmost digit into column D, I would simply use column D as the data labels (see "use cell values as data labels" here: https://support.microsoft.com/en-us/...2-f467c9f4eb2d ).
Originally Posted by shg
What is the point to have me give a full a explanation,post the sheet twice only to be given a documentation link?
Even I know the custom formatting is possible, since both of you said that as well and asked me for futher explanation is the reason why I did all that.
If you don't have the solution just don't make people do unnecessary things like this and end up giving them the documentation link.
Thanks
vonmunchy, your ill-tempered comments do not encourage anyone help.... especially since it was ME that asked for an explanation and Mr Shorty who supplied the link.
I don't see where Glenn or Mr S have said the formatting you describe IS possible. They've both said 'IF' it is possible.
Personally I don't think Excel custom formatting can do this. It might be possible to write a macro to interact with the chart series values and apply the background colour to all but the last character of each value. IF that is possible I sense it's not a two minute piece of coding. As Glenn implied a bit of humility wouldn't go amiss.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Okey. Sorry if I came off rude.
I've been away all day and am only looking at this again, now.
In my attachment, Post 8, the data labels ARE the last digit of the value. If I understand what you want, the only thing MISSING from what you want ... is a line joining sequential datapoints. Is that correct? Or am I missing something important.
So another guess...
Any closer? Formulae in shaded cells. I added another dataset (all) with no markers, just a line.
Thanks. That's kinda of where I got stuck. Anyways thanks for replying. Il figure an alternative
Rather an alternative what I can think of trying to achive what I want is to have two "cell from values". That way I can use two different sets of data because currently when I choose cell from values I'm only able to use 1
Can you explain again where it is incorrec and what you expect to see that is different.. I'm clearly misunderstanding what you want. However, I'm only here for about the next 10 mins... It's time to find a restaurant for dinner!!
I want last digit for each of the cloum to appear separately so I can use series formatting to give it a color like that
Hi, appogies again for not being clear enough before. I was under the impression the "value from cell" is for all data labels and didn't know I can have separate ones for each data series.
Below attached is much clearer example of what I was trying to achieve and I managed to do so, but now a new minor issue. It doesn't show the first values from range
Is this it???
I resolved it. Will Close the thread. Thanks
Yes perfect Thanks a lot. Appreciate it
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks