Hi!

Here's a couple of things to try:

Assume your numbers look like this:

A1 = 100:<space>

In a helper cell enter this formula:

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

You might also want to test and make sure the <space> is a standard space:

=CODE(RIGHT(A1))

The code for a standard ascii colon is 58.
The code for a standard space is 32.

If you get any other results then try this:

Let's assume the first formula returns a code number of 208 (just a random
number I picked).
The second formula returns a code of 160 (this is a non-breaking space,
common in HTML)

Select the range of cells in question
Goto Edit>Replace
Find What: hold down the ALT key and using the numeric keypad type 02080160
Replace With: nothing, leave this blank
Replace All

Another thing to try:

Select the cells in question
Goto Data>Text to Columns
Delimited
Next
Delimiters: Select Other and in the little box enter a colon :
Next
In the next screen at the bottom you'll see how Excel wants to split the
data.
Click on the second column header, GENERAL
Then in the upper right there's a selection: Do not import column, select
that
Finish

Biff

"ModelerGirl" <ModelerGirl.22hh7a_1138684800.4719@excelforum-nospam.com>
wrote in message
news:ModelerGirl.22hh7a_1138684800.4719@excelforum-nospam.com...
>
> I have a column of numbers, but each contains a colon at the end (was
> pasted in from an HTML file). I'd like to have excel treat each one as
> a number. Is there a way to have excel automatically remove the colon
> and space at the end of each number?
>
> Search and replace does not seem to work, since it doesn't seem to
> "find" the colons.
>
> Thanks!
>
>
> --
> ModelerGirl
> ------------------------------------------------------------------------
> ModelerGirl's Profile:
> http://www.excelforum.com/member.php...fo&userid=6364
> View this thread: http://www.excelforum.com/showthread...hreadid=506660
>