Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing comma in data string
Try:
with range(yourrange) ..value = .value end with Cliff Edwards |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using comma inside the comma delimited text in Data Validation/Sou | Excel Programming | |||
removing comma | Excel Worksheet Functions | |||
Removing a space after a comma | New Users to Excel | |||
Removing every second instance of comma sign | Excel Programming | |||
Data string ends up in first cell instead of Comma sepaerated CSV format | Excel Programming |