Problem with indata mixture of text and numbers

[Jowel]

Hi
I am working with an excel sheet that gets its data from a database, that I cannot affect the output from.
Everything would be easy if the data just been correctly formatted from the beginning, but it isn't.

The database sends everything out in some sort of text, and also the cells that appear blank/empty isn't. Normally it is just to convert it to numbers, but sometimes people miss to do that or miss some number, and keeps working.
I have tried to make a formula that would be able to warn if people they have missed something but so far i have failed.

As can be seen in the workbook the data is in one sheet and the calculations is done in another. In this case I have already converted most numbers but left some of the errors.
E.g. cell N3 in Indata needs to be converted to numbers, and N7 changed to 10.
What I want is that until that is done it will say "Lacking or incorrect information in N column in data sheet" in G1 and G3 in the calculation sheet.

Anyone know anyway to make that happen?

Thanks in advance.

[oeldere]

You can run this macro.

Please Login or Register  to view this content.

[Jowel]

Thank you very much oeldere. Will try it out.

[Jowel]

Hi
I have been trying it out and it work good aslong as it is a whole number.
But as soon as it is decimal in it like 3.5 it becomes 35
I am using . as a decimal seperator, dont know if that has anything to do with it?

[oeldere]

please add an excel file, with your problem, so we can test it.

[Jowel]

Sorry that i didn't reply quicker.

As can be seen in the file I get the problem for N9 and N10. In N9 it becomes 932 instead of 93.2 and N10 stays unconverted.

[oeldere]

In that case you could

Select the whole column N.

CTRL + H

Find . (dot)
Replace by , (komma)

Replace all.

The you get the result you want.

[Jowel]

Thank you for the help.

It turned out like this in the end:

Please Login or Register  to view this content.

Thank you for all the help.