An easy solution I use (it's elementary but works) is in a new column simply
enter the formula =(trim(a1))+0

This will take out any leading or following spaces, then by adding 0 it will
convert it to a number without changing it. I usually then copy/paste values
in that new column, then cut and paste it over the original. Again, it's
basic, but only takes less than a minute to do.

" wrote:

On Jan 3, 3:09 pm, "Matt Williamson" wrote:
The following two functions should take care of what you need.

Set (your_range) as range

for each cell in your range

cell.value = curval
curval = Replace(curval, " ", "") ' removes any spaces you might
have
curarray = Split(curval, ",", -1, vbTextCompare) 'creates an array
of the values that are separated by the commas
arraynumb = UBound(curarray) 'determines how many values are in the
array

'process the information

next cell

There's probably a better way of taking care of it, but this method
seems to be working well in my current project

Regards,

Thedude

Well, I was able to get rid of the commas using Dave's method, but the
problem now is that it still won't recognize the data as numbers (I
also have a few percentages in the next column, and those won't
recognize either. If I try to add a comma, or decimal points, it
refuses to do it. Any thoughts?

Do they have a little green triangle in the upper left corner? If so,
they're text fields that happen to be numbers. An easy way to fix it is to
copy a completely blank (non formatted) cell, then select the range of
"text" numbers you're having trouble with and using edit | paste special.
Select the add option and click ok. You will loose leading zero's though, if
that's a concern. But that can be fixed with a custom number format.

If that's not the case, I'd use Edit | Clear | formats to try and clear any
funky formatting issues in those cells.

HTH

Matt

It doesn't. Looks like they all have a space in front of the number. I
think that's the reason for the problem. Any way to quickly delete all
spaces in front? I could do it manually, but there's like 5,000 cells