SOLVED

Formula

New Contributor
NumberMaterialBatch
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

@bsepelak 

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

Enter the following formula in D2:

 

=TEXT(A2,"00")&"_"&B2&"_"&C2

 

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)
Solution

@bsepelak 

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

 

=TEXT(A2,"00")&"_"&TRIM(B2)&"_"&C2

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