| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String to number
Importing from database to Excel numbers are converted to strings (ex 676 921,5) I've tried to convert this to number by using "Paste Special" - Multiply. Did not work. I then tried "Replace(" ","") and neither did this work. I do think that the "space" in the number is not the normal "space" (Unicode (Hex) 0020) but the "No-Breake Space" (Unicode (Hex) 00A0). So if anybody could give me a hint of how to solve this problem (VBA by preference) I would be verry happy. F2 works of cource but editing several columns with 500 rows is not a job I'm looking forward to. Alf -- Alf ------------------------------------------------------------------------ Alf's Profile: http://www.excelforum.com/member.php...fo&userid=7112 View this thread: http://www.excelforum.com/showthread...hreadid=482662 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String to number
Take a look at Dave McRitichie's TrimAll macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- HTH RP (remove nothere from the email address if mailing direct) "Alf" wrote in message ... Importing from database to Excel numbers are converted to strings (ex 676 921,5) I've tried to convert this to number by using "Paste Special" - Multiply. Did not work. I then tried "Replace(" ","") and neither did this work. I do think that the "space" in the number is not the normal "space" (Unicode (Hex) 0020) but the "No-Breake Space" (Unicode (Hex) 00A0). So if anybody could give me a hint of how to solve this problem (VBA by preference) I would be verry happy. F2 works of cource but editing several columns with 500 rows is not a job I'm looking forward to. Alf -- Alf ------------------------------------------------------------------------ Alf's Profile: http://www.excelforum.com/member.php...fo&userid=7112 View this thread: http://www.excelforum.com/showthread...hreadid=482662 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String to number
Perhaps you should remove the comma too.
"Alf" wrote in message ... Importing from database to Excel numbers are converted to strings (ex 676 921,5) I've tried to convert this to number by using "Paste Special" - Multiply. Did not work. I then tried "Replace(" ","") and neither did this work. I do think that the "space" in the number is not the normal "space" (Unicode (Hex) 0020) but the "No-Breake Space" (Unicode (Hex) 00A0). So if anybody could give me a hint of how to solve this problem (VBA by preference) I would be verry happy. F2 works of cource but editing several columns with 500 rows is not a job I'm looking forward to. Alf -- Alf ------------------------------------------------------------------------ Alf's Profile: http://www.excelforum.com/member.php...fo&userid=7112 View this thread: http://www.excelforum.com/showthread...hreadid=482662 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String to number
The comma represents a decimal point in his regional settings
or at least wherever he got his data. Not everyone uses the US Regional settings. However that does bring up an interesting point. How will the VBA in the TrimALL macro treat this. I think it will work fine because it is simply reentering a value. "Simon Chang" wrote... Perhaps you should remove the comma too. "Alf" wrote in message ... Importing from database to Excel numbers are converted to strings (ex 676 921,5) I've tried to convert this to number by using "Paste Special" - Multiply. Did not work. I then tried "Replace(" ","") and neither did this work. I do think that the "space" in the number is not the normal "space" (Unicode (Hex) 0020) but the "No-Breake Space" (Unicode (Hex) 00A0). So if anybody could give me a hint of how to solve this problem (VBA by preference) I would be verry happy. F2 works of cource but editing several columns with 500 rows is not a job I'm looking forward to. Alf -- Alf ------------------------------------------------------------------------ Alf's Profile: http://www.excelforum.com/member.php...fo&userid=7112 View this thread: http://www.excelforum.com/showthread...hreadid=482662 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String to number
On Sun, 6 Nov 2005 14:25:19 -0600, Alf
wrote: Importing from database to Excel numbers are converted to strings (ex 676 921,5) I've tried to convert this to number by using "Paste Special" - Multiply. Did not work. I then tried "Replace(" ","") and neither did this work. I do think that the "space" in the number is not the normal "space" (Unicode (Hex) 0020) but the "No-Breake Space" (Unicode (Hex) 00A0). So if anybody could give me a hint of how to solve this problem (VBA by preference) I would be verry happy. F2 works of cource but editing several columns with 500 rows is not a job I'm looking forward to. Alf Try this: =--TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",""),CHAR(160),"")) --ron |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String to number
Thanks Ron !!! Your formula works like charm. Have not had a chanse to test your link Bo, early morning now in Swede and I must rush to work. And yes as David pointed out we do use the comm as decimal point here. Again thanks to all of you for your help -- Al ----------------------------------------------------------------------- Alf's Profile: http://www.excelforum.com/member.php...nfo&userid=711 View this thread: http://www.excelforum.com/showthread.php?threadid=48266 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String to number
On Sun, 6 Nov 2005 23:37:26 -0600, Alf
wrote: Thanks Ron !!! Your formula works like charm. Have not had a chanse to test your link Bo, early morning now in Sweden and I must rush to work. And yes as David pointed out we do use the comma as decimal point here. Again thanks to all of you for your help. Glad to help. Thanks for the feedback. --ron |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String to number
Hi David I tried your macro TrimAll but I'm sorry to say I did'n work as well a Ron's formula. I'm runing Office 2003 English version with Swedish key board. If I highlighted a cell after runnig "TrimAll" I got the followin "error" message: "The number in this cell is formated as text or proceded by a apostrophe." Excel also gives me the option to convert this to numbers, which i does without any problem. If I edit the cell placing my cursor in fron of the first nummber an do a "back space" it also converts the cell content to a number. Finaly I put 3 numbers in a cell and used "Insert - Symbol - No-Brea Space" and added 4 more numbers in the same cell. I then tried your "TrimAll" macro on this cell but got the same resul as before. If I do a division or a multiplication using a "treated" and a "normal cell the result transforms into a number. Hope this feedback is of use to you. Al -- Al ----------------------------------------------------------------------- Alf's Profile: http://www.excelforum.com/member.php...nfo&userid=711 View this thread: http://www.excelforum.com/showthread.php?threadid=48266 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number of Occurrences of a String Value within a String | Excel Discussion (Misc queries) | |||
| Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
| counting the number of instances of a string within another string | Excel Worksheet Functions | |||
| Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
| converting number string to number with decimal | Excel Worksheet Functions | |||
![Alf[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
