Find & replace duplicate digits with in a cell
Results 1 to 4 of 4

Thread: Find & replace duplicate digits with in a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Find & replace duplicate digits with in a cell

    I would like to find specific digits in a cell and replace it with a symbol or some text like "duplicate", not the whole cell only the duplicate characters in the cell.
    I have one column with more then 2500 rows which contains contact numbers. Every cell contains more then one number. then i have a another column in which i have the numbers that i want to be deleted from the first column.
    I am placing the example so everybody can get an idea of what i want.


    Column 1 looking for duplicates in this one Column 2 Numbers to find from first column and replace with symbol

    051-5506664,051-5518677,0331-5689456
    0331-5689456
    ,0301-6278688, 0300-6245388,0331-5689457
    0331-5689457
    0537610640,0333-8564866,0331-5689451
    0331-5689458
    ,0344-5329610 / 0044-7864922101,0331-5689458
    0331-5689459
    544-646789/345-5624363,0331-5689459
    0331-5689450
    ,051-5826034,0331-5689450
    0331-5689451

    I am new to fourms, Could not find the attachment button to attach excel file, so made a table in the post. Help in this regard is highly appreciated. Thanks
    Last edited by Fluff; Jul 13th, 2019 at 11:31 AM. Reason: Updated op

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    599
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find & replace duplicate digits with in a cell

    Is what you're looking for (column B) always the string after the last comma in column A?
    And, what happens when the string in column B is not found in column A?
    Last edited by kweaver; Jul 13th, 2019 at 12:56 PM.

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    599
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find & replace duplicate digits with in a cell

    Is it something like this?

    Excel 2010
    ABCDE
    1ORIGINAL STRINGFINDWHEREREPL WITHNEW STRING
    2051-5506664,051-5518677,0331-56894560331-568945624AAA051-5506664,051-5518677,AAA
    3,0301-6278688, 0300-6245388,0331-56894570331-568945728BBB,0301-6278688, 0300-6245388,BBB
    40537610640,0333-8564866,0331-56894510331-5689458#VALUE!CCC0537610640,0333-8564866,0331-5689451
    5,0344-5329610 / 0044-7864922101,0331-56894580331-5689459#VALUE!DDD,0344-5329610 / 0044-7864922101,0331-5689458
    60537610640,0333-8564866,0331-56894510333-856486611EEE0537610640,EEE,0331-5689451

    Sheet4



    Worksheet Formulas
    CellFormula
    C2=FIND(B2,A2)-1
    E2=IF(ISERROR(C2),A2,LEFT(A2,C2)&D2&RIGHT(A2,LEN(A2)-(C2+LEN(B2))))


  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,673
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Find & replace duplicate digits with in a cell

    Post a link to shared excel file with your representative example of source and expected result. Use GoogleDrive, OneDrive, DropBox or any similar service.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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