Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
EricKei
 
Posts: n/a
Default How do I force leading zeros in an Excel cell?

I am attempting to force numeric values exported from a QuickBooks Point of
Sale 3.0 Report into an Excel spreadsheet to take on leading zeros in order
to "line up" properly when the spreadsheet is later exported to text. The
cells used are all Text format, and have been exported from QBPOS as Text
Only. I need to add four leading zeros to each one, for a total of 5 digits
per cell. I intend to use the resulting Sheet as a template for future QBPOS
Reports to export to. QBPOS itself does not have a funtion to Export to text
directly (only to Excel), which is why I am using Excel as a go-between.
There is an option which forces *trailing* zeros in cells, but I cannot find
one for *leading* zeros. Would I be better off running this through Access
instead, ie, QBPOS' Export - Excel - Export to Access - Export to TXT?

Also, how would I cause the cells in each row to be Merged in such a way
(when exorted to unicode TXT) that they will all appear in one long string of
numbers, rather than as groups of numbers with spaces between them? The usual
Merge deletes all values after the first cell.

Thank you in advance for any advice offered.
  #2   Report Post  
Barb R.
 
Posts: n/a
Default

Let's say you have the value "12" in the cell, and you want it to be
displayed as "00012". Format the cell (or cells) as Custom with format
00000.

"EricKei" wrote:

I am attempting to force numeric values exported from a QuickBooks Point of
Sale 3.0 Report into an Excel spreadsheet to take on leading zeros in order
to "line up" properly when the spreadsheet is later exported to text. The
cells used are all Text format, and have been exported from QBPOS as Text
Only. I need to add four leading zeros to each one, for a total of 5 digits
per cell. I intend to use the resulting Sheet as a template for future QBPOS
Reports to export to. QBPOS itself does not have a funtion to Export to text
directly (only to Excel), which is why I am using Excel as a go-between.
There is an option which forces *trailing* zeros in cells, but I cannot find
one for *leading* zeros. Would I be better off running this through Access
instead, ie, QBPOS' Export - Excel - Export to Access - Export to TXT?

Also, how would I cause the cells in each row to be Merged in such a way
(when exorted to unicode TXT) that they will all appear in one long string of
numbers, rather than as groups of numbers with spaces between them? The usual
Merge deletes all values after the first cell.

Thank you in advance for any advice offered.

  #3   Report Post  
EricKei
 
Posts: n/a
Default

"Barb R." wrote:

Let's say you have the value "12" in the cell, and you want it to be
displayed as "00012". Format the cell (or cells) as Custom with format
00000.


Thanks! :)
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
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Paste from Word table so that 1 table cell = 1 Excel cell Dave G Excel Discussion (Misc queries) 4 June 17th 05 12:16 AM
leading zeros in excel kiwi Excel Discussion (Misc queries) 3 May 17th 05 06:59 PM
Excel file saved as csv - dropping zeros Kathy Excel Discussion (Misc queries) 3 February 18th 05 08:15 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 10:44 PM.

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"