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

[turtleth]

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
Reference	Count	Status
A.1.1.1.1 Leadsheet	1	Open
A.3.1.01 PPLC Policies and Procedures	1	Open
B.1.2.01 Setting New Accounts in  NetX555	1	Open
B.1.2.3 Systematic Entitlements to NetX555	1	Open
B.1.2.10.1 Leadsheet	2	Open
B.11.1.02 Receivable / Payable Open Items Report	1	Addressed
B.11.1.2 Receivable / Payable Open Items Report	2	Open
B.13.1.7 Month-end Statement Reports	1	Open

What I want it to be
Reference	Count	Status
A.1.1.01 Leadsheet	1	Open
A.3.1.01 PPLC Policies and Procedures	1	Open
B.1.2.01 Setting New Accounts in  NetX555	1	Open
B.1.2.03 Systematic Entitlements to NetX555	1	Open
B.1.2.10 Leadsheet	2	Open
B.11.1.02 Receivable / Payable Open Items Report	1	Addressed
B.11.1.02 Receivable / Payable Open Items Report	2	Open
B.13.1.07 Month-end Statement Reports	1	Open

Best,

M

[danrh · Accepted solution]

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!

[turtleth]

Hi Danrh,

Thank you so much!!! 

It worked like a magic. I have been stuck with this for days and could not figure it out. Again, truly appreciate it!! Hope you have a very nice weekend.

Best,

M