How to delete and replace a character in a specific position in a cell?

Question

Hi there,

Hope you all had a wonderful 4th of July.

I am trying to solve a problem over here for work. I am trying to delete the numbers after the 4th period and add a "0" in the front of the digits from 1-9. An example would be from

"A.1.1.1.1 to A.1.1.01"  Please see the below for the data information. Any of your inputs will be deeply appreciated. Thank you!

Original Data  ReferenceCountStatusA.1.1.1.1 Leadsheet1OpenA.3.1.01 PPLC Policies and Procedures1OpenB.1.2.01 Setting New Accounts in  NetX5551OpenB.1.2.3 Systematic Entitlements to NetX5551OpenB.1.2.10.1 Leadsheet2OpenB.11.1.02 Receivable / Payable Open Items Report1AddressedB.11.1.2 Receivable / Payable Open Items Report2OpenB.13.1.7 Month-end Statement Reports1Open

What I want it to be  ReferenceCountStatusA.1.1.01 Leadsheet1OpenA.3.1.01 PPLC Policies and Procedures1OpenB.1.2.01 Setting New Accounts in  NetX5551OpenB.1.2.03 Systematic Entitlements to NetX5551OpenB.1.2.10 Leadsheet2OpenB.11.1.02 Receivable / Payable Open Items Report1AddressedB.11.1.02 Receivable / Payable Open Items Report2OpenB.13.1.07 Month-end Statement Reports1Open

Best,

M

danrh · Accepted Answer

This should do the trick:

The RegEx tool splits out the field into four parts (the first section that needs no change, the second section that needs an additional "0", the third section that needs to be removed, and the fourth that needs no change).  The Formula tool adds the "0" and re-combines the fields, and the Select tool gets rid of extra fields.  Give it a go!