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

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

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

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

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

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

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

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