In a recent Excel question I was given a formula to use which worked very well and I'm so greatfull it did because it got us out of a huge bind.

LEFT(TRIM([Test]),LEN(TRIM([Test]))-6) & IF(LEN(TRIM([Test]))>6," ","") & RIGHT(REPLACE(TRIM([Test]),".",","),6)

I was just wondering why so many TRIM functions were used on the Test variable. is this standard procedure to make sure the numbers don't get messed up by extra spaces etc?

pspdfppd,

I guess it was put there in case there would be spaces. Because this formula is put on a public newsgroup and anyone can use it, it may have been put there to ensure that anyone who copies the data would be able to use the formula without errors, even if their data have spaces. Also, since we don't have your data in hand, he might have wanted to write something that would be certain to work in all cases, spaces or not.

In other words, it's just extra safety to make sure that the expression worked when you copy it. If you don't need the trims, feel free to remove them.

Regards,
Rapha

Thanks alot now it makes sense.

As it turned out, the excel file DID have spaces all over the place so the formula would NOT have worked with all the trim functions. (unless we had stripped them out of the excel file beforehand. (i think))