Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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?
0
zachvaldez
Asked:
zachvaldez
  • 5
  • 5
  • 4
2 Solutions
 
barry houdiniCommented:
If you want to get rid of everything after the space do an "Edit/Replace" replacing

[space][asterisk]

with nothing

regards, barry
0
 
sungenwangCommented:
You can put this formula to the right of each cell and copy down:
=LEFT(A2,FIND(" ",A2))

This will remove all the text after (and include) the space.
Then you do a paste value.

sew
0
 
zachvaldezAuthor Commented:
what do you mean by
"to the right of each cell and copy down"
thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
barry houdiniCommented:
Did you try the Edit > Replace?

That will amend the data in situ rather than having to create another column......

barry
0
 
sungenwangCommented:
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
0
 
zachvaldezAuthor Commented:
I use 2010.
0
 
sungenwangCommented:
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
0
 
zachvaldezAuthor Commented:
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.
0
 
sungenwangCommented:
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

0
 
barry houdiniCommented:
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
0
 
zachvaldezAuthor Commented:
barry how to do that with 2010 version
0
 
barry houdiniCommented:
Select the column with your text then on "Home" tab, all the way to the right hand side of the ribbon, select "Find and Select" then from the menu "Replace".

In the "Find what" box you need to put a space followed by an asterisk (*), just those two characters, levae the "Replace with" box empty.

Select "Replace All" > OK > Close

regards, barry
0
 
zachvaldezAuthor Commented:
What if text trailing the numbers have more than 1 space in between
Like
66666 Railroad Trans
So here for example has 2 spaces...
0
 
barry houdiniCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now