How to remove a character at the end of each number in a long range

I have a column of numbers, but each contains a colon at the end (e.g. "1223:").. 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!

Did you try copying the colons and pasting into the find what box? Works
very well, usually. But they must not be "real" colons, 'cause I can
find/replace real ones... Hm. Interesting!
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

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

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

Hi

The space may the non-breaking space (nsbp) often used in HTML format
which is Char(160)
To find out what the colon is, assuming your data is in column A, use
=CODE(MID(A1,LEN(A1)-2,1)
to find what that last space really is then use
=CODE(RIGHT(A1))
Supposing the results of this came up as 58 and 160

Then in another column enter
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(58),"")
and this should eliminate the extraneous characters for you.
Put the double unary minus -- in front of the formula to coerce the text
result to a numeric value.
=--SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(58),"")
Copy down the new column for as far as required.
Then, copy this new column of data and Paste Special>Values back over
the original set of values in column A


--
Regards

Roger Govier


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