identify character that looks like a space

[choeg]

Hi,

(sorry for posting the same question twice, but the topic has changed, since the problem has changed)

Hi,

I've been trying to remove all "spaces" from a text string, but can't get it to work. I just figured out why I am having problems... the "spaces" are not space characters, but they look like them!

Problem:
I paste info from another application into Excel in the following format.
e.g. "100 000.00" (could also be 100 000 000.10, or many other combos).
This is interpreted as text in Excel, since there are "spaces" hard coded in between the numbers.

Goal:
Have Excel interpret this as a number. (e.g. "100000").

It seems like it's not spaces I am dealing with, but some other character that looks like a space... why do I think so? because this function does not work on my pasted data, but it works if I type in "100 000.00" (with a space):

=VALUE(SUBSTITUTE(E2;" ";""))


I am using Excel 97. I don't mind using VBA, but would prefer not to.

Any help would be appreciated.

/Carl

 

[Mudface]

You could return the ASCII code associated with your 'space' by, for example, posting the string containing the 'space' into A1 and using the following formula: -

=CODE(MID(A1,4,1))

You could then use the code returned in your SUBSTITUTE formula, eg if it returns 32-

=VALUE(SUBSTITUTE(A1,CHAR(32),""))

 

[choeg]

thanks a lot "mudface", that solved my problem!
the character was "160".. which one is that!?

//C

 

[Mudface]

According to the Insert Symbol dialog, it's a non-breaking space (whatever that is )...

 

[pint]

According to the Insert Symbol dialog, it's a non-breaking space (whatever that is )...

non-breaking space is a space where you cannot break the text into new line if on the end of the row. if you would use normal space, the number would be split into two if eventually appears on the end of a line. non breaking spaces also don't stretch when justified. used for numbers and codes, where space is the part of the code (here, in hungary, ID card numbers are formatted as AU 475345 or something like that.

 

[Mudface]

Ahhh, so AU 475345, say, would always appear on one line, gotcha