Deleting first word on all rows in column 1 ?
Hi all !
I have a big task ahead of me and hopefully someone will be able to save me from having to go cell by cell.
Every row/cell in Column A consists of the word "star" and a family name (eg. STAR JONES). I need to remove the word "star".
Is there a formula applicable to this ?
Thanks,
Kai
Last edited by Kai; 09-06-2012 at 04:10 PM.
Kai,
Welcome to the forum!
Because it is the same every time, you can use the following formula (and copy down):
Alternately, you can use Data -> Text to Columns -> Fixed Width -> Next -> Move the arrow after the space -> Next -> With the "Star " column highlighted, select the option "Do not import column (skip) -> Finish
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I would use
CNTRL + H
find star
replace by "" (leave blanc)
Replace all
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
re: oekdere's suggestion, make sure it's Star with a blank after it, otherwise all your cells wll begin with a blank
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Wow, thanks for the speedy and helpful replies !
This one worked like a charme:
Data -> Text to Columns -> Fixed Width -> Next -> Move the arrow after the space -> Next -> With the "Star " column highlighted, select the option "Do not import column (skip) -> Finish
Unfortunately I only tried the ctrl+H afterwards. I think it would have been even faster.
I do have one more issue which is not solved with the above. I also have a column which consists of last, first, middle names: eg. SMITH JOHN MARK.
Some of the last names are longer. Therefore moving the arrows does not work. Is there a way to either remove the first word (last names with different lengths) or better yet, even just singleing out the second word and removing the rest ?
I appreciate all your help !!!
If they are always three names, you can use the text to columns again. Only this time choose "Delmited" -> Next -> check "Space" -> Next -> Highlight the first column and select "Do not import colun (skip)" and then do the same for the third column -> Finish
Thank you so much ! Even though I do not have 3 names in all the rows, it worked perfectly !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks