Counting and substituting

Hi,

I am working on a database where the lookup requires numbers to be in a set format and set number of charters.

The first letter will either be E, O or I and then it will be followed by a “.” and then 6 digits. E.g. E.000123

However the data I am using isn’t always in the correct format. What I need is a formula that will count the number of numbers between the two “.” And then add zeros after the first dot to ensure there are 6 numbers.

e.g. E.123 would become E.000123

I have attached an example

Can anyone help with the formula?

Regards

Paul

H Paul

Try in F4:

=LEFT(D4,2)&TEXT(MID(D4,3,FIND(".",D4,3)-3),"000000")

Thanks all for the fomrulas!

I apologise my requirement has already changed since writing this post slightly.

I require all the information after the 2nd "." to also be returned.

e.g. E.123.01.01

Should be E.000123.01.01

I have uploaded a file with more examples. Can the formulas you orignally provided be amended to include this addition?

Perfect - thanks So much Ace

Another option, expanding my formula:

=LEFT(D4,2)&TEXT(MID(D4,3,FIND(".",D4,3)-3),"000000")&MID(D4,FIND(".",D4,3),LEN(D4))