Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 15th 09, 05:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 4
Default REPLACE command drops leading zeros

I have some part numbers (see examples below) that contain hypens that I need
to remove for approx. 800 records. If I edit the cells and remove the hypens
manually the leading zero is dropped because Excel assumes it's a number. If
i format the cell as TEXT first then repeat the process the leading zero is
retained.

Since I have so many records to remove the hypens from I wanted to use the
FIND/REPLACE menu option. When I do this I lose the leading zeros regarless
of whether or not I format the cells as TEXT beforehand.

Does anyone have any idea why this is happening? I thought it may be a bug
so I tried in both Excel 2003 and 2007 with the same results. I also thought
that maybe there is some hidden formatting in the spreadsheet that may be
tripping me up (I've seen this before in Excel) so I copied and PASTE AS
VALUES the data to a new Excel file with the same results.

Any help would be much appreciated.

Guy

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027
014259858-0101-063
009833612-0101-085


  #2   Report Post  
Old October 15th 09, 05:44 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,942
Default REPLACE command drops leading zeros

XL does an implicit conversion when you Find/Replace. There is no setting or
option to avoid it. Since they are part numbers there is no value in
converting them to numbers. I would recommend using the Substitute formula
something like this...

=SUBSTITUTE(A2, "-", "")

Where your part number is in A2.
--
HTH...

Jim Thomlinson


"Guy Kerr" wrote:

I have some part numbers (see examples below) that contain hypens that I need
to remove for approx. 800 records. If I edit the cells and remove the hypens
manually the leading zero is dropped because Excel assumes it's a number. If
i format the cell as TEXT first then repeat the process the leading zero is
retained.

Since I have so many records to remove the hypens from I wanted to use the
FIND/REPLACE menu option. When I do this I lose the leading zeros regarless
of whether or not I format the cells as TEXT beforehand.

Does anyone have any idea why this is happening? I thought it may be a bug
so I tried in both Excel 2003 and 2007 with the same results. I also thought
that maybe there is some hidden formatting in the spreadsheet that may be
tripping me up (I've seen this before in Excel) so I copied and PASTE AS
VALUES the data to a new Excel file with the same results.

Any help would be much appreciated.

Guy

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027
014259858-0101-063
009833612-0101-085

  #3   Report Post  
Old October 15th 09, 05:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 345
Default REPLACE command drops leading zeros

Guy one solution copy this formula into a helper column and drag down.
Then Copy column Paste Special Values
Hope this helps


"Guy Kerr" wrote:

I have some part numbers (see examples below) that contain hypens that I need
to remove for approx. 800 records. If I edit the cells and remove the hypens
manually the leading zero is dropped because Excel assumes it's a number. If
i format the cell as TEXT first then repeat the process the leading zero is
retained.

Since I have so many records to remove the hypens from I wanted to use the
FIND/REPLACE menu option. When I do this I lose the leading zeros regarless
of whether or not I format the cells as TEXT beforehand.

Does anyone have any idea why this is happening? I thought it may be a bug
so I tried in both Excel 2003 and 2007 with the same results. I also thought
that maybe there is some hidden formatting in the spreadsheet that may be
tripping me up (I've seen this before in Excel) so I copied and PASTE AS
VALUES the data to a new Excel file with the same results.

Any help would be much appreciated.

Guy

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027
014259858-0101-063
009833612-0101-085

  #4   Report Post  
Old October 15th 09, 05:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default REPLACE command drops leading zeros

Check your other post.

Guy Kerr wrote:

I have some part numbers (see examples below) that contain hypens that I need
to remove for approx. 800 records. If I edit the cells and remove the hypens
manually the leading zero is dropped because Excel assumes it's a number. If
i format the cell as TEXT first then repeat the process the leading zero is
retained.

Since I have so many records to remove the hypens from I wanted to use the
FIND/REPLACE menu option. When I do this I lose the leading zeros regarless
of whether or not I format the cells as TEXT beforehand.

Does anyone have any idea why this is happening? I thought it may be a bug
so I tried in both Excel 2003 and 2007 with the same results. I also thought
that maybe there is some hidden formatting in the spreadsheet that may be
tripping me up (I've seen this before in Excel) so I copied and PASTE AS
VALUES the data to a new Excel file with the same results.

Any help would be much appreciated.

Guy

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027
014259858-0101-063
009833612-0101-085


--

Dave Peterson
  #5   Report Post  
Old October 15th 09, 05:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 4
Default REPLACE command drops leading zeros

Thank you both. The Substitute command worked beautifully. I don't know why
I didn't think of this. I got so caught up with trying to figure out why the
FIND/REPLACE wasn't working that I didn't look for a better solution.

Thanks again.

"Guy Kerr" wrote:

I have some part numbers (see examples below) that contain hypens that I need
to remove for approx. 800 records. If I edit the cells and remove the hypens
manually the leading zero is dropped because Excel assumes it's a number. If
i format the cell as TEXT first then repeat the process the leading zero is
retained.

Since I have so many records to remove the hypens from I wanted to use the
FIND/REPLACE menu option. When I do this I lose the leading zeros regarless
of whether or not I format the cells as TEXT beforehand.

Does anyone have any idea why this is happening? I thought it may be a bug
so I tried in both Excel 2003 and 2007 with the same results. I also thought
that maybe there is some hidden formatting in the spreadsheet that may be
tripping me up (I've seen this before in Excel) so I copied and PASTE AS
VALUES the data to a new Excel file with the same results.

Any help would be much appreciated.

Guy

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027
014259858-0101-063
009833612-0101-085



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
Using REPLACE command removes leading zeros Guy Kerr Excel Discussion (Misc queries) 2 October 15th 09 05:23 PM
csv format drops ending zeros Excel 2007 - CSV format Excel Discussion (Misc queries) 2 June 23rd 09 12:26 PM
Excel drops my lead zeros: need 0051 not 51 Monet Excel Discussion (Misc queries) 3 December 26th 07 11:06 PM
line graph drops when zeros Maxwell_5000 Charts and Charting in Excel 1 June 30th 05 12:38 AM
csv file drops zeros at beginning of a set of Numbers Lisa Excel Discussion (Misc queries) 2 January 8th 05 09:53 PM


All times are GMT +1. The time now is 02:19 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017