More involved find and replace of dashes and update bases on non-numeric numbers

  • Updated request for finding and replacing based on non-numeric entry and removable of dashes.

    Sample: Original

    Account Number: 2 Rolls of Stamps
    Account Number: 16758967 IN
    Account Number: 444555-65-009
    Account Number: 123456790101

    Sample Updated to

    Description: 2 Rolls of Stamps
    Description: 16758967 IN
    Account Number: 44455565009
    Account Number: 123456790101

  • @Jeff-Brewer ,

    Thank you for example before and after that included both what to change and what not to change. Using the </> button – as advised last time – would format data as plain text, and make sure that we can trust the characters we see are the same as what you pasted in (the forum changes some characters like making "ascii quotes" turn into “smart quotes”), and so we can know how many spaces are between words, and other such things. The </> button protects your data from the forum.

    I would accomplish this in a two-step process:

    1. Get rid of hyphens in Account numbers
      • Find = (?<=Account Number: )(\d+)-(\d+)
      • Replace = $1$2
      • Mode = regular expression
      • Replace or Replace All multiple times, until “0 occurrences were replaced”
    2. Follow the answer to the previous question

    Some might work regex magic to combine those all into one, or to get rid of the need to hit Replace All more than once. But I usually use (and recommend) multiple sequential regexp rather than a supe-complicated one that you’ll never remember how or why it works.

    And I highly recommend reading the resources linked in my previous reply so that you can start learning regex on your own, rather than having to rely on someone else to craft them for you.

  • In a two-step process:

    Find = : (\d+)-(\d+)-(\d+)
    Replace = : $1$2$3

    Find = (Account Number): (.*[a-z].*)
    Replace = Description: $2

  • Hello, @jeff-brewer, @peterjones, @olivier-thomas and All,

    Here is my solution :

    SEARCH Account Number(:\h*.*[\l\u])|-

    REPLACE ?1Description\1

    Note : When a dash is matched, the group 1 does not exist. As the conditional replacement ?1Description\1 does not contain any else part, any dash character is then deleted !

    Best Regards,


Log in to reply