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?
zachvaldezAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
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
 
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
 
sungenwangConnect With a Mentor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
zachvaldezAuthor Commented:
what do you mean by
"to the right of each cell and copy down"
thanks
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.