removing comma in data string

[jkrich]

I have a string of numbers, but they've been entered as:

357,041
332,224
341,038

etc.

Basically I'd just like to convert them to numbers so I can add,
subtract, etc. The problem is that as they are right now, they are
stored as text, and I can't figure out how to fix that (format cells >
number does nothing).

Please help.

 

[Dave Peterson]

How about selecting the range to fix
edit|Replace
what: , (a comma)
with: (leave blank)
replace all

 

[itsthedude]

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

 

[jkrich]

How about selecting the range to fix
edit|Replace
what: , (a comma)
with: (leave blank)
replace all

Got rid of the commas, didn't solve the text/number situation. For
some reason it refuses to store it as a number.

 

[jkrich]

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?

 

[jkrich]

How about selecting the range to fix
edit|Replace
what: , (a comma)
with: (leave blank)
replace all

I really can't format anything. I tried making it a currency, date,
time, anything other than what it is, and it won't do it.

Any thoughts?

 

[Matt Williamson]

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

 

[jkrich]

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

 

[ward376]

Try:

with range(yourrange)
..value = .value
end with

Cliff Edwards

 

[Dave Peterson]

If you put these in unused cells:
=len(a1)
=isnumber(a1)
(Change A1 to the cell that is having trouble)

What is returned (for the 357041 cell)?

If you don't see 6 for the =len() formula, then you probably won't see True for
the =isnumber() formula.

My bet is that you have other characters in those cells.

If the data came from the web, you could have those non-breaking HMTL spaces in
your cell.

David McRitchie has a macro that can help clean up:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[jkrich]

If you put these in unused cells:
=len(a1)
=isnumber(a1)
(Change A1 to the cell that is having trouble)

What is returned (for the 357041 cell)?

If you don't see 6 for the =len() formula, then you probably won't see True for
the =isnumber() formula.

My bet is that you have other characters in those cells.

If the data came from the web, you could have those non-breaking HMTL spaces in
your cell.

David McRitchie has a macro that can help clean up:http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:http://www.mvps.org/dmcritchie/excel/getstarted.htm

I did:

trim(substitute(A2,char(160),"")) and that worked perfectly to
eliminate the space at the front.

 

[Dave Peterson]

I saw this in your other thread.

You may want to use:
=--trim(substitute(A2,char(160),""))
so the result of the formula is a number--not text.

 

[joebogey]

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.