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?

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" wrote:

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?

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.



Paul B wrote:

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

Thanks that did it

"Dave Peterson" wrote in message
...
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.



Paul B wrote:

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

Which one?

I'm curious.

Paul B wrote:

Thanks that did it

"Dave Peterson" wrote in message
...
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.



Paul B wrote:

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

--

Dave Peterson

Hi Dave,

They should both work!

--
Thanks,
Shane Devenshire


"Dave Peterson" wrote:

Which one?

I'm curious.

Paul B wrote:

Thanks that did it

"Dave Peterson" wrote in message
...
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.



Paul B wrote:

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

--

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.

ShaneDevenshire wrote:

Hi Dave,

They should both work!

--
Thanks,
Shane Devenshire

"Dave Peterson" wrote:

Which one?

I'm curious.

Paul B wrote:

Thanks that did it

"Dave Peterson" wrote in message
...
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.



Paul B wrote:

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

--

Dave Peterson


--

Dave Peterson