SOLVED

cell formating

%3CLINGO-SUB%20id%3D%22lingo-sub-1979408%22%20slang%3D%22en-US%22%3Ecell%20formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1979408%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20fill%20in%20cell%20with%20code%20like%20that%3A%3C%2FP%3E%3CP%3E-%206%20digits%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E-%206%20digits%20%2B%20*%3C%2FP%3E%3CP%3EI%20want%20to%20type%20it%20like%20for%20exemple%20011008%20and%20have%20a%20result%20like%20that%2001%2010%2008%3C%2FP%3E%3CP%3EUntil%20now%20I%20use%20the%20cell%20formating%200%23%20%23%23%20%23%23....and%20it%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20i%20use%20the%206%20digits%20%2B%20%22*%22%20like%20for%20exemple%20011008*...I%20obtain%20this%20result%26nbsp%3B%20011008*.%3C%2FP%3E%3CP%3EAnd%20I%20would%20like%20to%20obtain%2001%2010%2008*.%3C%2FP%3E%3CP%3EI%20have%20tried%20many%20formaat%20(0%23%20%23%23%20%23%23%23%2C%20...etc...)...unsuccessful%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20if%20there%20is%20a%20solution%20to%20obtain%20a%20formaat%20which%20accept%20both%20(with%20or%20without%20*)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anybody%20help%20me%3F%3C%2FP%3E%3CP%3EThx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1979408%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1979464%22%20slang%3D%22en-US%22%3ERe%3A%20cell%20formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1979464%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20use%20*%20in%20creating%20custom%20format%2C%20you%20should%20you%20use%20%5C%20before%20it.%3C%2FP%3E%3CP%3ESo%20try%20this%20format%3A%26nbsp%3B%26nbsp%3B0%23%20%23%23%20%23%23%20%23%23%5C*%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1980936%22%20slang%3D%22en-US%22%3ERe%3A%20cell%20formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1980936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F898816%22%20target%3D%22_blank%22%3E%40Nicolas1313%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%206%20digits%20only%20you%20have%20a%20number%2C%20apply%20to%20it%20custom%20%3CSTRONG%3Enumber%3C%2FSTRONG%3E%20format%20and%20it%20works.%3C%2FP%3E%0A%3CP%3E6%20digits%20with%20asterisk%20it%20text%2C%20not%20number%2C%20and%20custom%20number%20format%20doesn't%20work%20with%20texts%20(more%20exactly%20can't%20format%20them%20same%20way%20as%20numbers).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20to%20use%20formula%20and%20put%20desired%20result%20into%20another%20column%20or%20use%20VBA.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1992652%22%20slang%3D%22en-US%22%3ERe%3A%20cell%20formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1992652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThx%20for%20your%20answer%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I fill in cell with code like that:

- 6 digits

or

- 6 digits + *

I want to type it like for exemple 011008 and have a result like that 01 10 08

Until now I use the cell formating 0# ## ##....and it works.

 

But if i use the 6 digits + "*" like for exemple 011008*...I obtain this result  011008*.

And I would like to obtain 01 10 08*.

I have tried many formaat (0# ## ###, ...etc...)...unsuccessful

 

I would like to know if there is a solution to obtain a formaat which accept both (with or without *)

 

Can anybody help me?

Thx

4 Replies

Hi 

If you want to use * in creating custom format, you should you use \ before it.

So try this format:  0# ## ## ##\* 

@Jihad Al-Jarady 
Thx

but this format give me always the *
I would like to have the choice to type:

010101 and obtain 01 01 01

Or

010101* and obtain 01 01 01*

In your format I obtain always 01 01 01* 

best response confirmed by Nicolas1313 (New Contributor)
Solution

@Nicolas1313 

With 6 digits only you have a number, apply to it custom number format and it works.

6 digits with asterisk it text, not number, and custom number format doesn't work with texts (more exactly can't format them same way as numbers).

 

That's to use formula and put desired result into another column or use VBA.

@Sergei Baklan 

Thx for your answer