Adding zeroes to the values in certain format

[Bellzemos]

Hello,

I have an Excel spreadsheet which has a column with values like that:

1002.1.39
1004.2.112
1007.1.7
1008.4.195

I need to reformat it to look like that - the value after the second dot has to be a 5-space number, filled with zeroes as needed:

1002.1.00039
1004.2.00112
1007.1.00007
1008.4.00195

I have MS Excel 2016, how can I achieve this? Thank you!

[sourabhg98]

If the second dot is always the 7th character as shown by you you can use this formula-

Please Login or Register  to view this content.

However if position of second dot is unspecified use this formula-

Please Login or Register  to view this content.

[Bellzemos]

It's the first case, the second dot is always the 7th character but I can't get the formula to work.

I am terribe at Excel. I need this to be done from the column A from row A2 to A152, how do I do it?

Thank you!

[sourabhg98]

Okay no problems.
Check attached.excelforum-101.xlsx

Copy the formula to the end of your range in column B.

[Bellzemos]

Thank you very very much!