Number format within a formula

That's exactly what I was looking for, thank you!

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

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

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

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

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

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

>> When I opened the file and saw a formulas there

I don't understand. Can you flesh that out?

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

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.