• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Number format within a formula

On the attached file in cell B2 the value is 0.4 and diplayed as 0.400, in cell A2 I have a formula to display this value from B2 as it was shown on the worksheet.  If I use this formula the number shows as  0.4 instead of 0.400.  One caveat is that the source (B2) has to be in numerical format as it was used for target vs. actual comparisons in conditional logic.  I tried to create a custom format in VBA as a user defined function and applied within the formula but did not help.  How can I get the A2 displaying the value pulling from B2 and display it as 0.400 while applying the formula so I can have the same diplay as (<0.400/K instead of showing <0.4/K)? DisplayFormat.xlsx
0
trusxlsol
Asked:
trusxlsol
  • 5
  • 5
1 Solution
 
gowflowCommented:
Is this what your looking for ?
gowflow
DisplayFormat.xlsx
0
 
trusxlsolAuthor Commented:
That's exactly what I was looking for, thank you!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

as an alternative to the formula converting B2 into a text value with

="<"&TEXT(ROUND(B2,3),"0.000")&"/K"

you can also use =B2 and format it with custom format

<0.000\/K

See screenshot.

cheers, teylyn

3-10-2011-8-58-44-a.m..png
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gowflowCommented:
Yes teylyn that's correct (if it worked it didn't with me) but if asker need to use somehow these .000 in an other formula manipulation, then your suggestion won't work as only visual as value still in the field is .0

Actually I tried your suggestion format <0.000\/K (< 0.000 \ backslah / slash K) all together no spaces and still it gave me 0.4 (applied it on the asker's original formula: ="<"&ROUND(B2,3)&"/K"
gowflow
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
gowflow, this formula

="<"&ROUND(B2,3)&"/K"

returns a text value. You can't apply a custom format to text.  You can see in the screenshot that it works on numbers, but just for good measure, here's a workbook, too.


Book1.xlsx
0
 
gowflowCommented:
Your right. what I actually meant in your example is that if the user need to physically look for the character "<" or "/" or "K" to take out the number 1.300 say he will not be able like using =SEARCH("<",A2) will return value or any search of any charater for that matter and the amount is strictly showing as formated 0.000 but not physically it is still as inputed by the user 1.4 or 1.32 etc...
gowflow
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I see where you're coming from.

There are different approaches, depending on what the objective is.

Your suggestion was accepted, so it apparently does what the asker was after. That's great.

I posted my suggestion at the same time that the asker accepted yours. So, no sweat. I'm fine with that.

The custom format I submitted as an additional suggestion is based on the original question's statement

>>How can I get the A2 displaying the value pulling from B2 and display it as 0.400 while applying the formula so I can have the same diplay as (<0.400/K instead of showing <0.4/K)?

The operative here is "displaying". That's what the custom format I suggested does. It does not change the underlying value or data type, it just displays it in a certain way. This leaves the cell open to further processing, if need be, for example summing or other operations that require a numeric value, even if the display shows characters not normally found in a number.

There is no need to separate the result into number and characters surrounding it. It's still just a number.

I do not know enough about the background of the question to judge whether or not this suggestion is applicable in this situation, or if a transformation to a text value is preferable. I'm just showing an alternative to display the value in the specified format.

If further processing requires to use the number only, which feeds the cell, then they do not need to separate any text characters. If, though, a lookup is based on the "<" or "K" character, then my suggestion will not be the best fit.

But that's a moot point, since your suggestion to turn the cell value into text has been accepted. So you provided what the asker was after. Good job.

cheers, teylyn

0
 
gowflowCommented:
Teylyn
For "displaying".  your right on in the bulls eye and even better than what I suggested !!!
BUT,
When I opened the file and saw a formulas there .... just had to fix it to the asker's request.

Anyway I always like your interventions as always there is good things to learn from
Cheers
gowflow
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> When I opened the file and saw a formulas there

I don't understand. Can you flesh that out?
0
 
gowflowCommented:
Didn't you notice that the asker attached a file ? when I opened the attached file and saw that in column A there was a formula ="<"&ROUND(B2,3)&"/K" then I proceeded to fix the formula so the result was inline with the user's request !!!
Clear ?
gowlfow
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Yes, I noticed the file. I also noticed this sentence in the question

"I tried to create a custom format in VBA as a user defined function and applied within the formula but did not help."

So I offered a solution with a custom format.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now