Number format in a formula
 

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?

Number format in a formula
 

Try...

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

Hope this helps!

In article ,
Vicki wrote:

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?

Number format in a formula
 

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


Vaya con Dios,
Chuck, CABGx3




"Vicki" wrote:

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?

Number format in a formula
 

Vicki,

Try,

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

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=557028

Number format in a formula
 

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

On Thu, 29 Jun 2006 10:46:01 -0700, Vicki
wrote:

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?

Number format in a formula
 

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.

"CLR" wrote:

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


Vaya con Dios,
Chuck, CABGx3




"Vicki" wrote:

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?

Number format in a formula
 

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





"Vicki" wrote:

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.

"CLR" wrote:

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


Vaya con Dios,
Chuck, CABGx3




"Vicki" wrote:

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?

Number format in a formula
 

PERFECT! Thank you so much.

"CLR" wrote:

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





"Vicki" wrote:

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.

"CLR" wrote:

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


Vaya con Dios,
Chuck, CABGx3




"Vicki" wrote:

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?

Number format in a formula
 

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

Vaya con Dios,
Chuck, CABGx3



"Vicki" wrote:

PERFECT! Thank you so much.

"CLR" wrote:

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





"Vicki" wrote:

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.

"CLR" wrote:

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


Vaya con Dios,
Chuck, CABGx3




"Vicki" wrote:

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?