Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I get zeros to stay in front of a # when using the concate.
Hello all!
I'm trying to concatenate an invoice # with another field, and the problem I'm having is that Excel drops the 0's in front of numbers. I can get them to re-appear using custom formating, but when I use the concatenate or lookup functions, the 0's aren't really there so my opperation fails!?! HELP ME!!! Thanks in advance for your thoughts. Monk |
#2
|
|||
|
|||
"czarkzm" wrote in message ... Hello all! I'm trying to concatenate an invoice # with another field, and the problem I'm having is that Excel drops the 0's in front of numbers. I can get them to re-appear using custom formating, but when I use the concatenate or lookup functions, the 0's aren't really there so my opperation fails!?! HELP ME!!! Thanks in advance for your thoughts. Monk Let the first character be an apostrophe like this: '007 /Fredrik |
#3
|
|||
|
|||
See:
http://j-walk.com/ss/excel/usertips/tip037.htm Try using a format of =TEXT(A1,"00000000"). HTH Jason Atlanta, GA -----Original Message----- Hello all! I'm trying to concatenate an invoice # with another field, and the problem I'm having is that Excel drops the 0's in front of numbers. I can get them to re-appear using custom formating, but when I use the concatenate or lookup functions, the 0's aren't really there so my opperation fails!?! HELP ME!!! Thanks in advance for your thoughts. Monk . |
#4
|
|||
|
|||
You can use the text function
=TEXT(A1,"000")&B1 change the number of zeros to what you need or if you always want to pad with one leading zero =TEXT(A1,REPT("0",LEN(A1)+1))&B1 -- Regards, Peo Sjoblom "czarkzm" wrote in message ... Hello all! I'm trying to concatenate an invoice # with another field, and the problem I'm having is that Excel drops the 0's in front of numbers. I can get them to re-appear using custom formating, but when I use the concatenate or lookup functions, the 0's aren't really there so my opperation fails!?! HELP ME!!! Thanks in advance for your thoughts. Monk |
#5
|
|||
|
|||
Thanks! That's exactly what I was looking for.
Monk PS. Great site! Definitely going to bookmark that one! "Jason Morin" wrote: See: http://j-walk.com/ss/excel/usertips/tip037.htm Try using a format of =TEXT(A1,"00000000"). HTH Jason Atlanta, GA -----Original Message----- Hello all! I'm trying to concatenate an invoice # with another field, and the problem I'm having is that Excel drops the 0's in front of numbers. I can get them to re-appear using custom formating, but when I use the concatenate or lookup functions, the 0's aren't really there so my opperation fails!?! HELP ME!!! Thanks in advance for your thoughts. Monk . |
#6
|
|||
|
|||
Thanks Fredrik, but I have 30,000+ lines - looking for a formula since I'm
lazy! ;) "Fredrik Wahlgren" wrote: "czarkzm" wrote in message ... Hello all! I'm trying to concatenate an invoice # with another field, and the problem I'm having is that Excel drops the 0's in front of numbers. I can get them to re-appear using custom formating, but when I use the concatenate or lookup functions, the 0's aren't really there so my opperation fails!?! HELP ME!!! Thanks in advance for your thoughts. Monk Let the first character be an apostrophe like this: '007 /Fredrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
leading zeros in text format | Excel Discussion (Misc queries) | |||
Format a cell to keep leading zeros. | New Users to Excel | |||
how do i get excel to NOT take zero's of the front of a number | Excel Discussion (Misc queries) | |||
how can i get a cell to hold a zero in front of a digit ie. 01 or. | Excel Discussion (Misc queries) | |||
Displaying leading zeros in an Excel spreadsheet | Excel Discussion (Misc queries) |