removing comma in data string
 

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.

removing comma in data string
 

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



wrote:

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

removing comma in data string
 

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

removing comma in data string
 

On Jan 3, 2:28 pm, Dave Peterson wrote:
How about selecting the range to fix
edit|Replace
what: , (a comma)
with: (leave blank)
replace all



wrote:

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

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

removing comma in data string
 

On Jan 3, 2:33 pm, 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?

removing comma in data string
 

On Jan 3, 2:28 pm, Dave Peterson wrote:
How about selecting the range to fix
edit|Replace
what: , (a comma)
with: (leave blank)
replace all



wrote:

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

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?

removing comma in data string
 

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

removing comma in data string
 

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

removing comma in data string
 

Try:

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

Cliff Edwards

removing comma in data string
 

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

wrote:

On Jan 3, 2:28 pm, Dave Peterson wrote:
How about selecting the range to fix
edit|Replace
what: , (a comma)
with: (leave blank)
replace all



wrote:

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

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?

--

Dave Peterson

removing comma in data string
 

On Jan 3, 4:30 pm, Dave Peterson wrote:
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



wrote:

On Jan 3, 2:28 pm, Dave Peterson wrote:
How about selecting the range to fix
edit|Replace
what: , (a comma)
with: (leave blank)
replace all

wrote:

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

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?

--

Dave Peterson

I did:

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

removing comma in data string
 

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.


wrote:
<<snipped
I did:

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

--

Dave Peterson

removing comma in data string
 

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