Format for degrees

[Biff]

Hi folks!

Working on a project and using a formula like this just so we can get the
degree sign:

=TEXT(ROUND(DEGREES(ATAN2((C6-C30)/2,A25)),1),"0.0")&CHAR(176)

This obviously results in a text value.

How can I dump the TEXT function and the CHAR function and format the cell
to display the degree sign and still be a numeric data type?

Thanks!

Biff

[Elkar]

You can just add the ° symbol to your Custom Number format. Hold down the
Alt key and type 0176.

HTH,
Elkar


"Biff" wrote:

> Hi folks!
>
> Working on a project and using a formula like this just so we can get the
> degree sign:
>
> =TEXT(ROUND(DEGREES(ATAN2((C6-C30)/2,A25)),1),"0.0")&CHAR(176)
>
> This obviously results in a text value.
>
> How can I dump the TEXT function and the CHAR function and format the cell
> to display the degree sign and still be a numeric data type?
>
> Thanks!
>
> Biff
>
>
>

[Casey]

Biff,
Try
=VALUE(SUBSTITUTE(A1,"°",0))*B1 where A1 is the text with Chr (176). The text is converted to a number that can be used in another formula. By substituting the 0 (Zero) for the ° degree character you lose any background numerical accuracy.
Hint to enter the ° charater using the keyboard use "Alt 0176"

[Biff]

Hi!

Thanks for the reply. I can pretty much figure out ways to use the text
value in other calcs but I was looking (hoping) for a custom number format.
I'm not so good at creating custom formats!

Thanks!

Biff

"Casey" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
> Try
> =VALUE(SUBSTITUTE(A1,"°",0))*B1 where A1 is the text with Chr (176).
> The text is converted to a number that can be used in another formula.
> By substituting the 0 (Zero) for the ° degree character you lose any
> background numerical accuracy.
> Hint to enter the ° charater using the keyboard use "Alt 0176"
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile:
> http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=537324
>

[Biff]

Hi!

>You can just add the ° symbol to your Custom Number format.

Well, that's the problem! I don't know what that custom number format would
be!

Thanks!

Biff

"Elkar" <[email protected]> wrote in message
news:[email protected]...
> You can just add the ° symbol to your Custom Number format. Hold down the
> Alt key and type 0176.
>
> HTH,
> Elkar
>
>
> "Biff" wrote:
>
>> Hi folks!
>>
>> Working on a project and using a formula like this just so we can get the
>> degree sign:
>>
>> =TEXT(ROUND(DEGREES(ATAN2((C6-C30)/2,A25)),1),"0.0")&CHAR(176)
>>
>> This obviously results in a text value.
>>
>> How can I dump the TEXT function and the CHAR function and format the
>> cell
>> to display the degree sign and still be a numeric data type?
>>
>> Thanks!
>>
>> Biff
>>
>>
>>

[Casey]

Biff,
Go to Format>Cells>Number>Category Custom and Type:
0.00 ° Insert the ° using the keyboard entry from my first post. It works for me.
The entry into the cell either by typing or by formula puts a ° symbol after the number and yet the cell contents can still be used in other formulas because it reads like any other number.

Hope that is what you were looking for.

[Elkar]

Well, it appears that you want to display a number with one decimal place, so
your Custom Format should look like this:

0.0°

Or is there something I'm missing?

HTH,
Elkar


"Biff" wrote:

> Hi!
>
> >You can just add the ° symbol to your Custom Number format.
>
> Well, that's the problem! I don't know what that custom number format would
> be!
>
> Thanks!
>
> Biff
>
> "Elkar" <[email protected]> wrote in message
> news:[email protected]...
> > You can just add the ° symbol to your Custom Number format. Hold down the
> > Alt key and type 0176.
> >
> > HTH,
> > Elkar
> >
> >
> > "Biff" wrote:
> >
> >> Hi folks!
> >>
> >> Working on a project and using a formula like this just so we can get the
> >> degree sign:
> >>
> >> =TEXT(ROUND(DEGREES(ATAN2((C6-C30)/2,A25)),1),"0.0")&CHAR(176)
> >>
> >> This obviously results in a text value.
> >>
> >> How can I dump the TEXT function and the CHAR function and format the
> >> cell
> >> to display the degree sign and still be a numeric data type?
> >>
> >> Thanks!
> >>
> >> Biff
> >>
> >>
> >>
>
>
>

[Peo Sjoblom]

format>cells>number>custom

Type

0.0

then hold down alt key while typing 0176 on the numpad, release alt key

Peo



"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
>>You can just add the ° symbol to your Custom Number format.
>
> Well, that's the problem! I don't know what that custom number format
> would be!
>
> Thanks!
>
> Biff
>
> "Elkar" <[email protected]> wrote in message
> news:[email protected]...
>> You can just add the ° symbol to your Custom Number format. Hold down
>> the
>> Alt key and type 0176.
>>
>> HTH,
>> Elkar
>>
>>
>> "Biff" wrote:
>>
>>> Hi folks!
>>>
>>> Working on a project and using a formula like this just so we can get
>>> the
>>> degree sign:
>>>
>>> =TEXT(ROUND(DEGREES(ATAN2((C6-C30)/2,A25)),1),"0.0")&CHAR(176)
>>>
>>> This obviously results in a text value.
>>>
>>> How can I dump the TEXT function and the CHAR function and format the
>>> cell
>>> to display the degree sign and still be a numeric data type?
>>>
>>> Thanks!
>>>
>>> Biff
>>>
>>>
>>>
>
>

[Gary''s Student]

Hi Biff:


Let's say that in A1 we have 30.123456
this is a little more than 30 degrees.

In A2 put =A1/24
and format A2:
Format > Cells... > Number > Custom and enter
[hh]°mm'ss\"
in place of
General
to see: 30°07'24"





--
Gary''s Student


"Biff" wrote:

> Hi!
>
> Thanks for the reply. I can pretty much figure out ways to use the text
> value in other calcs but I was looking (hoping) for a custom number format.
> I'm not so good at creating custom formats!
>
> Thanks!
>
> Biff
>
> "Casey" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Biff,
> > Try
> > =VALUE(SUBSTITUTE(A1,"°",0))*B1 where A1 is the text with Chr (176).
> > The text is converted to a number that can be used in another formula.
> > By substituting the 0 (Zero) for the ° degree character you lose any
> > background numerical accuracy.
> > Hint to enter the ° charater using the keyboard use "Alt 0176"
> >
> >
> > --
> > Casey
> >
> >
> > ------------------------------------------------------------------------
> > Casey's Profile:
> > http://www.excelforum.com/member.php...fo&userid=4545
> > View this thread: http://www.excelforum.com/showthread...hreadid=537324
> >
>
>
>

[Biff]

Well, all I can say is........Doh!

Sometimes I'm so "thick" it's not funny. LOL!

Thanks to everyone for responding.

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi folks!
>
> Working on a project and using a formula like this just so we can get the
> degree sign:
>
> =TEXT(ROUND(DEGREES(ATAN2((C6-C30)/2,A25)),1),"0.0")&CHAR(176)
>
> This obviously results in a text value.
>
> How can I dump the TEXT function and the CHAR function and format the cell
> to display the degree sign and still be a numeric data type?
>
> Thanks!
>
> Biff
>