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

Jeff Brewer

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

PeterJones

@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:

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”
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.

Olivier Thomas

In a two-step process:

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

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

guy038

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,

guy038