Remove character at end of data

[Paul B]

I have a column of data in the form of 1-1, 1-2, … 1-123, it has a space at
the end of the numbers, not really a space, I can copy the "space" and paste
it in another cell and then use = code and I get a code of 160. how can I
get this out from behind all the data in column B. I have copied the "space"
and did a replace on the column but then all the cells format as a date,
even if I format the column to text first. How can I do this and not have
the data change to a date?

 

[Dave Peterson]

You could use a helper column and then use a formula like:

=substitute(b1,char(160),"")
(and drag down the column)
to remove all the HTML non-breaking spaces.

Then select that column and do edit|copy
select the original column B and edit|paste special|Values
and delete the helper column.

or...

You could also toggle a Lotus 123 setting.
In xl2003 menus:
Tools|options|Transition tab|check "Transition formula entry"

Then select the range
Edit|replace
what: alt-0160 (hit and hold the alt key while you type 0160 using the number
pad)
with: (leave blank)

Then toggle that setting off.

 

[Sheeloo]

Suppose you have your data in Col A
Then in B11 type
=Left(A1,Len(A1)-1)
Format Col B as TEXT
Copy Col B, click on B1 and choose Edit|Paste Special|Values

 

[Paul B]

Thanks that did it

You could use a helper column and then use a formula like:

=substitute(b1,char(160),"")
(and drag down the column)
to remove all the HTML non-breaking spaces.

Then select that column and do edit|copy
select the original column B and edit|paste special|Values
and delete the helper column.

or...

You could also toggle a Lotus 123 setting.
In xl2003 menus:
Tools|options|Transition tab|check "Transition formula entry"

Then select the range
Edit|replace
what: alt-0160 (hit and hold the alt key while you type 0160 using the
number
pad)
with: (leave blank)

Then toggle that setting off.

 

[Dave Peterson]

Which one?

I'm curious.

Thanks that did it

 

[ShaneDevenshire]

Hi Dave,

They should both work!

 

[Dave Peterson]

Yep.

I was curious about which technique the OP used.

The formula (either of them) or the edit|replace with the Lotus 123 setting
toggle.

Hi Dave,

They should both work!

 

[Paul B]

Dave, I used your formula
Thanks

Yep.

I was curious about which technique the OP used.

The formula (either of them) or the edit|replace with the Lotus 123
setting
toggle.

 

[Dave Peterson]

Thanks for the response.

You may want to try that Lotus 123 toggle technique. You'll be able to impress
your friends and family <vbg>.