+ Reply to Thread
Results 1 to 8 of 8

Find and replace on the 1st few number with zero

  1. #1
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    109

    Find and replace on the 1st few number with zero

    Hi guys,

    I got a list of code whereby there are some code looks like below,

    I know if I want to remove the "200-", just do a CTRL+H, type 200- on the Find What, and blank on the Replace With:

    This will eliminate code with any 200-

    Now what I would like to know is for example 200-11-200-7, can i remove the 200- on the first few number,so that it turns out to be 11-200-7

    200-11-200-7
    200-11-200-8
    200-11-200-9
    200-11-200-10
    200-11-200-11
    200-11-1800-7
    200-11-1800-8
    200-11-1800-9

    Regards
    cks1026

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Find and replace on the 1st few number with zero

    Use a Helper column with this formula
    =RIGHT(A1,LEN(A1)-4)
    and copy down the column
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Find and replace on the 1st few number with zero

    =replace(a1,1,find("-",a1),"")

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Find and replace on the 1st few number with zero

    hi cks1026, i doubt the Find & Replace can help you. an alternative formula:
    =MID(A1,5,LEN(A1))

    then copy & paste values over if you need to

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Find and replace on the 1st few number with zero

    Hi Special-K,

    That's a great help!

    Now what if I would like to other way round, I want the code to be in "200-11-7", meaning to remove the "200-" in the middle?
    The code may not be in such length, sometime could be longer / shorter.

    Thanks in advance.

  6. #6
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Find and replace on the 1st few number with zero

    Hi Guys,

    sorry for being absence for so long...

    Anyone could suggest some solution here?

    Appreciated !

    Quote Originally Posted by cks1026 View Post
    Hi Special-K,

    That's a great help!

    Now what if I would like to other way round, I want the code to be in "200-11-7", meaning to remove the "200-" in the middle?
    The code may not be in such length, sometime could be longer / shorter.

    Thanks in advance.

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Find and replace on the 1st few number with zero

    it seemed like you were directing your question just to Special-K in post #5, without including Ghozi & me. try this:
    =SUBSTITUTE(A1,"-200-","-")

    do mark the thread as "Solved" if that answers your question. if you have a question not pertaining to subject topic, do start a new thread

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find and replace on the 1st few number with zero

    Try this,
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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