String to number

[Alf]

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

 

[Bob Phillips]

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)

 

[Simon Chang]

Perhaps you should remove the comma too.

 

[David McRitchie]

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.

Perhaps you should remove the comma too.

 

[Ron Rosenfeld]

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

 

[Alf]

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

 

[Ron Rosenfeld]

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

 

[Alf]

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