Excel function to remove/edit strings in a column
In the location column,
I have the following data displayed..
666666 Railroad
666666 Trains
666666 Buses
....
Id like to edit the data in that column to remove the trailing text..Railroad,Trains and Buses
It would be cumbersome to go to its line and manulally remove it. What's excel trick to to do?
I have the following data displayed..
666666 Railroad
666666 Trains
666666 Buses
....
Id like to edit the data in that column to remove the trailing text..Railroad,Trains and Buses
It would be cumbersome to go to its line and manulally remove it. What's excel trick to to do?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what do you mean by
"to the right of each cell and copy down"
thanks
"to the right of each cell and copy down"
thanks
Did you try the Edit > Replace?
That will amend the data in situ rather than having to create another column......
barry
That will amend the data in situ rather than having to create another column......
barry
If you have the location column in A, then put the formula to column B, then copy the formula down. See attached workbook.
sew
Book1.xls
sew
Book1.xls
ASKER
I use 2010.
try this one... same formula will work.
also, don't forget to "copy value" to the entire output column. this way it will get rid of the formula and replace it with the new value.
sew
Book1.xlsx
also, don't forget to "copy value" to the entire output column. this way it will get rid of the formula and replace it with the new value.
sew
Book1.xlsx
ASKER
wihtout readiing the last post, I tried to delete the original column and I got error because the values refeence the original cells. so it had to be absolute values when pasted.
before deleting the original column, do this first:
1. highlight the entire output column, right click and select Copy
2. right click and select Paste Special
3. check Value and OK
then you can delete the original column.
sew
1. highlight the entire output column, right click and select Copy
2. right click and select Paste Special
3. check Value and OK
then you can delete the original column.
sew
If you use the method I suggested then you don't need an additional column it just deleted all unwanted data in one shot - do you need help to implement?
barry
barry
ASKER
barry how to do that with 2010 version
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What if text trailing the numbers have more than 1 space in between
Like
66666 Railroad Trans
So here for example has 2 spaces...
Like
66666 Railroad Trans
So here for example has 2 spaces...
Using my suggestion will remove everything including and after the first space so you'd be left with just the number, 66666, is that what you want?
If there are no spaces in a cell then it won't change.
Did you try it?
regards, barry
If there are no spaces in a cell then it won't change.
Did you try it?
regards, barry
[space][asterisk]
with nothing
regards, barry