Formatting Cells For Invoice Number Format

[Quietman]

Hello I Am Trying To Complete A Spreadsheet For Work And One Collum Is For
Invoice Numbers In The Following Format 000/111-2222. Letters sometimes apear
as well as numbers so is there a way of formatting the collum so that the /
and - do not need to be typed. Hope this makes sense.

QM

 

[Don]

you will have to do the typing in a differnt column and concatinate in the
"/". I am not sure from your example the variations , but from what I see
you can type in "000111-2222" in A2 have "'/" in A1 (make sure you have a
single tick then the /"
then in the cell you want say B2, have
=left(a2,3)&$a$1&mid(a2,3,40)

good luck

 

[Don]

sorry should be
=LEFT(A2,3)&$A$1&MID(A2,4,40)

 

[Quietman]

Hello,

I was thinking more like when you format a column for a date for example,
you type 010208 and it apears as 01/02/08.
I would like to type my invoice number without the / or the -
I type A011112222 and it apears as A01/111-2222 for example
If this is not do able I may just continue to type in full but this would be
much slower.

Thanks for your help so far,

Regards, Ian

 

[Gord Dibben]

There is no input mask/format that will allow you to enter 010208 and have Excel
consider it to be a valid date of 01/02/08

Custom 00"/"00"/"00 will show 01/02/08

Format as date and Excel will return 12-12-1927

Back to your original request...............

You can Custom Format numbers but not text.

If the letter would always be the same you could format as

"A"00"/"000"-"0000

Enter 11112222 to get A01/111-2222


Gord Dibben MS Excel MVP