Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
czarkzm
 
Posts: n/a
Default 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   Report Post  
Fredrik Wahlgren
 
Posts: n/a
Default


"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   Report Post  
Jason Morin
 
Posts: n/a
Default

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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
czarkzm
 
Posts: n/a
Default

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   Report Post  
czarkzm
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
leading zeros in text format BigBrook Excel Discussion (Misc queries) 5 December 11th 08 09:00 PM
Format a cell to keep leading zeros. Shadyhosta New Users to Excel 5 July 27th 05 04:37 PM
how do i get excel to NOT take zero's of the front of a number tor Excel Discussion (Misc queries) 2 February 16th 05 05:35 AM
how can i get a cell to hold a zero in front of a digit ie. 01 or. Southern Belle Excel Discussion (Misc queries) 2 February 2nd 05 10:56 PM
Displaying leading zeros in an Excel spreadsheet marianthelibrarian Excel Discussion (Misc queries) 1 January 25th 05 03:08 PM


All times are GMT +1. The time now is 10:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"