Number format in a formula

[Guest]

If I have a cell formula =LEFT(B5,6), how do I apply a format to it as well.

I would like the result to read 00-000-000. I can't change the format to
this after I get the result from this. So can I incorporate that in the
formula code somewhere so it goes into my cell in this format?

 

[Domenic]

Try...

=TEXT(LEFT(B5,6),"00-000-000")

Hope this helps!

 

[Guest]

=TEXT(LEFT(B5,6),"00-000-000")


Vaya con Dios,
Chuck, CABGx3

 

[SteveG]

Vicki,

Try,

=TEXT(LEFT(B5,6),"00-000-000")

HTH

Steve

 

[Gord Dibben]

The formula returns the 6 digits as a text string.

What we do is coerce the results to be a number.

Change the formula to =LEFT(B5,6)*1

Now format as 00-000-00


Gord Dibben MS Excel MVP

 

[Guest]

Thank you very much. That did work, but now I am realizing I have a few
exceptions which will probably call for an IF statement. Some of the numbers
I need to convert are shorter than others.

So for these two examples.

4633155113 (needs to convert to 00-463-315) and this formula works for that

13324645061 (needs to convert to 01-332-464). This formula is coverting it
to 00-133-246.

So, how do I identify the different formulas in these cases? The values in
my cells have either 10 or 11 characters in them. So can I create an IF
statement based on that?

Thank you.

 

[Guest]

Try this...........

=IF(LEN(B5)=12,TEXT(LEFT(B5,6),"00-000-000"),"01-"&TEXT(LEFT(B5,5),"000-000"))

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

PERFECT! Thank you so much.

Try this...........

=IF(LEN(B5)=12,TEXT(LEFT(B5,6),"00-000-000"),"01-"&TEXT(LEFT(B5,5),"000-000"))

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

You're welcome, and thank you kindly for the feedback.

Vaya con Dios,
Chuck, CABGx3