Concatenate 2 columns

Originally Posted by snb

one reason could be:

Sub snb()
   [M2:M3000] = [if(E2:E3000="","",Text(E2:E3000,"000-") & text(F2:F3000,"#.##"))]
End Sub

If you want to get rid of the last dot in some cells:

Sub snb_002()
   [M2:M200] = [if(E2:E200="","",substitute(substitute(Text(E2:E200,"000-") & text(F2:F200,"#.##~"),".~",""),"~",""))]
End Sub

Thanks snb, that did do what we were looking for... any chance the ~ symbol could be implemented in the formula bar?

We have actually found a different solution using textpad.
Source of the problem was the xls file given to us has been modified with so many different formats that the ArcGIS was not able to read it properly.

By "purging" the xls file we got it to work :
1) Deleting all non-pertinent columns.
2) Copy pertinent columns into textpad.
3) Find/replace all tabs with commas.
4) Save as a txt file.
5) Open a fresh Excel session, open the txt file as delimited by comma.
6) Save as a new xls file.
7) Run the Join function process in ArcGIS as normally done.
8) Success!

Thank you for all your contributions, we learned a few new Excel tricks along the way which are always good to know.