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

[ModelerGirl]

I have a column of numbers, but each contains a colon at the end (wa
pasted in from an HTML file). I'd like to have excel treat each one a
a number. Is there a way to have excel automatically remove the colo
and space at the end of each number?

Search and replace does not seem to work, since it doesn't seem t
"find" the colons.

Thanks

 

[Anne Troy]

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

 

[Biff]

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

 

[Roger Govier]

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