+ Reply to Thread
Results 1 to 6 of 6

How to keep leading zeros when using find and replace

  1. #1
    Registered User
    Join Date
    11-15-2018
    Location
    La Crete, Alberta
    MS-Off Ver
    2016
    Posts
    14

    How to keep leading zeros when using find and replace

    I have a long list of numbers that end in -DOC. I'm trying to delete the -DOC using find and replace but when I do that, It gets rid of the leading zero.

    ex. 00344-DOC turns into 344

    My column is formatted as text but for some reason find and replace cancels that. The numbers in my list are all different lengths so custom formatting doesn't work.

    Why does excel hate zeros so much? I mean if someone didn't want them there are so many formats and tools to get rid of them easily however, the way it is currently set up, it is so hard for people who do want them, to keep them.

  2. #2
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: How to keep leading zeros when using find and replace

    If your data is in Column B use this formula in Column C and drag down:

    =LEFT(B2,LEN(B2)-4)

    Sorry about that
    Last edited by xjohnson; 11-15-2018 at 02:48 PM.

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to keep leading zeros when using find and replace

    I suppose your data in column A. If not, adjust the range for your data.
    Use in a new column with the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    drag down, copy values, paste values to column A, and remove the column with the formula.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: How to keep leading zeros when using find and replace

    a different way using find and replace would require you run it twice. Once with find >> 00 and replace with >>'00 then do a replace all, then the next find replace would find the -doc and replace leaving blank. Haven't tested it for more than 2 leading zeros but it works for your posted example.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    11-15-2018
    Location
    La Crete, Alberta
    MS-Off Ver
    2016
    Posts
    14

    Re: How to keep leading zeros when using find and replace

    Quote Originally Posted by José Augusto View Post
    I suppose your data in column A. If not, adjust the range for your data.
    Use in a new column with the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    drag down, copy values, paste values to column A, and remove the column with the formula.
    Thanks José Augusto, that worked perfectly.
    Thanks to you as well xjohnson. Your way worked too but I also have numbers that don't have "-DOC" at the end so José Augusto's way worked better for that.
    Sambo kid thanks for the reply but I need to import the excel sheet into a program and I don't want that apostrophe in there.

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to keep leading zeros when using find and replace

    You are welcome.

    Thanks for the rep. .)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Removing Leading Zeros, maintaining zeros after decimal
    By Waycool86 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-17-2015, 01:15 PM
  2. [SOLVED] Telephone numbers - Remove leading zeros or replace 07 with 447
    By jkirkham in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-12-2012, 07:48 AM
  3. Replies: 5
    Last Post: 10-10-2012, 06:04 AM
  4. [SOLVED] Find/Replace strips out zeros
    By mhlangensiepen in forum Excel General
    Replies: 4
    Last Post: 08-08-2012, 07:51 PM
  5. Replace leading zeros
    By brandnewday10 in forum Excel General
    Replies: 6
    Last Post: 09-01-2010, 10:14 AM
  6. [SOLVED] Find 1 word and replace it and leave the leading zero
    By Leslie in forum Excel General
    Replies: 2
    Last Post: 03-08-2006, 12:10 PM
  7. Replies: 1
    Last Post: 05-04-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1