Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove data that preceeds a character | Excel Worksheet Functions | |||
How to remove character format under .csv? | Excel Discussion (Misc queries) | |||
Remove ' character from copied excel cell to match data | Excel Discussion (Misc queries) | |||
Remove character from imported data | Excel Discussion (Misc queries) | |||
How to remove a character from the first index? | Excel Discussion (Misc queries) |