add decimal point

This is as per your result but you said

it adds a "." 3 characters in from the right

then there will be different formula. pls clarify.

Thanks

ditto  !!!!

Ignore my comment. itjockey has already posted exactly the same formula.

for both I get a formula error, I changed the cells accordinly. Could this be to do with how the current column is formatted, and the formula column is formatted? both are currently formatted "general".

Nope .....where is your data located i.e column?

AA2:200, and in AB2I put the formula and changed values accordinly and get this error:

http://www.treeplan.com/images/formula-typed-error.jpg

file is formatted csv, excel 2010.

Rob H - I even get the same formula error when running your query!

That error message normally suggests you have missed something from the formula and Excel is unable to determine and correct the error automatically, eg missing brackets where it is not obvious where the missing brackets should be or inclusion of text within double quotes that are not closed out.

Thanks
Rob H

this is crazy, I created a new spreadsheet, copied and pasted some sample data and still I get the same error. to any formula, what the hell?

Which nationality version of Excel are you using? Some versions require semi-colon ";" separator in the formula rather than comma ",".

Thanks
Rob H

its just british version of excel.

this is crazy, if I open excel on another machine, run the same query it works fine. So its like some issue with the software.

anyone got a theory why that formula would work on one installation of excel and not another. could it be a global setting in excel affecting this?

normal formulas i.e. =a1/2 work, it just seems to be text based formulas like len, right, left etc.

nor can I even do a search and replace, I must have pressed some shortcut key combo and its screwed things up.

can you post your original xls file with only the column that you want to change???