Remove character at end of data
 

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?

Remove character at end of data
 

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

Remove character at end of data
 

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?

Remove character at end of data
 

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

Remove character at end of data
 

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

Remove character at end of data
 

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

Remove character at end of data
 

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

Remove character at end of data
 

Dave, I used your formula
Thanks
"Dave Peterson" wrote in message
...
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

Remove character at end of data
 

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.

Paul B wrote:

Dave, I used your formula
Thanks
"Dave Peterson" wrote in message
...
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

--

Dave Peterson