New Contributor
0104.333.420          202817         
0204.334.565          204258         
0303.127.016          H800695-01     

Is there a way to take these columns and format them to 01_04.333.420_202817?

4 Replies


Let's say the data are in column A to C, starting in row 2.

Enter the following formula in D2:




Fill down to the last row with data.

Thank you so much for the help. there is a space between the .420 and the last _. Is there a way to get rid of that?
best response confirmed by Sergei Baklan (MVP)


That suggests that the cells in column B might contain a trailing space. To remove it:



Perfect, you just saved me hours of work! Thank you so much!