Convert Text to Number on 4,508 excel files.

K

KCHurd

Hi all.

I'm faced with a rather unique problem.I have a large amount of
improperly formatted excel files. They all have various cells formatted
as text, yet they contain numbers and perform calculations on the
cells. I now need to get these in a proper state.

What I need is:

Conversion of all text boxes containing numbers over to proper format
of number.
Do it automagically. I don't relish the thought of opening each one
individually and fixing it.

Any thoughts, tips or ideas on this?
 
P

PY & Associates

As we have no clue of how the sensitive spreadsheet look like, our
suggestion is

put all files in directory ...\01
get first filename - dir(*.xls)
loop for each file
open thisfile
loop for each sheet

you are on your own here
locate cells with the formula which can lead you to likely range of
value formatted as text

multiply this range with +1
next sheet
save thisfile to directory ...\02
close thisfile
get next filename - dir
next file

Regards
 
Top