Text function

[nk18]

Hello,

I am trying to format a number as text using the TEXT function. The problem I have is I want to format it in the following way (the brackets are just notes by the way!):

[h] (a number in hours) "h" (the letter "h"): mm (a number in minutes) m (the letter "m")

The desired result would be 12h:30m.

However, the TEXT function treats the "h" and "m" as numbers, so the above example would be:

1212:3030

Does anyone know the correct syntax to acheive this?

Thanks in advance.

PS
I need to use the TEXT function and not just custom number formats.

 

[Dan71]

Hi, I had a similar problem the other day. You have to actualy put your h and m in "" as you have on this board. It will treat them as text then.
Dan

 

[Richard Schollar]

Hi Nk18

You can use:

=TEXT(A1,"hh\h:mm\m")

Best regards

Richard

 

[nk18]

Hello again,

Thanks for both of the replies.

Dan, I tried using "", however, as the TEXT function needs the "" anyway for number formats I would get an error.

Richard, that works perfectly. Would you mind explaining what the "\" does or is any information located in the help file about it? I only ask as I would like to have a leading minus sign if the number is below zero (this is the reason I am using the TEXT function as it can appear to display time as negative).

Thanks again for the help.

 

[Richard Schollar]

Hi

The backslah is an 'escape' character that means Excel doesn't try to interpret the following character as a formatting element (otherwise you could never include # or 0 or h or m in the formatted text that is produced. I'm not aware of information on this being in the Help file (may be wrong on this) - I have Barry Houdini to thank for introducing me to the backslash in the Text function.

I think Dan was referring to a custom number format rather than in the actual TEXT function itself.

Richard

 

[nk18]

Hi Richard,

Thanks for the explanation that's solved my problem. To get a leading minus I just put "\-h\h:mm\m". Have a nice day and thanks again.