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)


"Alf" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Simon Chang]

Perhaps you should remove the comma too.


"Alf" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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.


"Simon Chang" <[email protected]> wrote...
> Perhaps you should remove the comma too.
>
>
> "Alf" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

[Ron Rosenfeld]

On Sun, 6 Nov 2005 14:25:19 -0600, Alf
<[email protected]> 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

[Alf]

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.

[Ron Rosenfeld]

On Sun, 6 Nov 2005 23:37:26 -0600, Alf
<[email protected]> 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

[Alf]

Hi David

I tried your macro TrimAll but I'm sorry to say I did'n work as well as 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 following "error" message:

"The number in this cell is formated as text or proceded by an apostrophe."

Excel also gives me the option to convert this to numbers, which it does without any problem.

If I edit the cell placing my cursor in fron of the first nummber and 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-Break Space" and added 4 more numbers in the same cell.

I then tried your "TrimAll" macro on this cell but got the same result 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.

Alf