07-25-2014, 07:59 AM | #1 | ||
Coordinator
Join Date: Jan 2001
Location: Not too far away
|
Excel Help
I have an excel sheet with values about books. A sample value might look like:
ACORN 24 ADVENTURE SUPERHEROES INCREDIBLES WALKER
The last word is always going to be the author. I need to do some manipulation about the other information. Is there a way I can generate the data without that last word? |
||
07-25-2014, 08:20 AM | #2 |
College Starter
Join Date: Oct 2000
Location: Pittsburgh, PA
|
If that data is in one cell, you could do text to columns with a space delimiter to split it and then work with the data and perhaps concatenate it back to together.
__________________
"It's a great day for hockey" - "Badger" Bob Johnson |
07-25-2014, 08:22 AM | #3 |
College Starter
Join Date: Dec 2006
|
Is it formatted like "TITLE - AUTHOR"?
If so, you can use LEFT function to show only the title. The number of characters would be counted by the FIND function (in this case, find the "-"). |
07-25-2014, 08:36 AM | #4 | |
Coordinator
Join Date: Mar 2002
Location: Dayton, OH
|
Quote:
This. But do a count in the columns so that you can make only 4 or 5 formulas, one for each number of words in the title, then when you concatenate, leave the last column off of the formula... then delete all but the concatenated column when you are done. Sounds convoluted, there's probably an easier way, but I could do that pretty quickly in the time it would take to research another solution. Though this would only work if the titles were always formatted the same way, and the last column was only ALWAYS the author.
__________________
My listening habits Last edited by Butter : 07-25-2014 at 08:37 AM. |
|
07-25-2014, 08:44 AM | #5 |
Coordinator
Join Date: Jan 2001
Location: Not too far away
|
So the last word is always the author. That is true. The parts in front of it can vary widely in length. So you might have 7 other words in front of it or 2 or 12.
Butter, I think you get at this but I'm not sure how... |
07-25-2014, 09:16 AM | #6 |
Coordinator
Join Date: Mar 2002
Location: Dayton, OH
|
Do Text to Columns on all with "Space" as the delimiter.
Add 2 columns. In first column, run "COUNTA" on all of the next columns that may have information. Sort by first column descending. You'll get the number of words descending. Then in the 2nd added column, use CONCATENATE to concatenate all but the last word... so if it's 10 words, you would concatenate the first 9. Copy that formula down until the number of words changes. Remove the last concatenated column from the formula, and copy down. Repeat until done. You also probably want to add a space in between each concatenated word. So it would be something like =CONCATENATE(C2," ",D2," ",E2) for 3 words (actually 4, since you'd be dropping the last one) so that the title would still have the proper spacing. Then when the # of words goes to 3, it would be =CONCATENATE(C2," ",D2) That's a brute force solution. Not elegant, but it should work.
__________________
My listening habits Last edited by Butter : 07-25-2014 at 09:18 AM. |
07-25-2014, 10:13 AM | #7 |
Coordinator
Join Date: Jan 2001
Location: Not too far away
|
Thanks for the help. I was able to use the basic concept of putting them into columns and then through some aggressive filtering was able to delete out the info and put it back together.
|
07-25-2014, 11:03 AM | #8 |
College Prospect
Join Date: Nov 2006
Location: High and outside
|
There has to be a better way than parsing it out and putting it all back. ...And there is. This formula. Full disclosure, I found this on the intarwebs:
=LEFT(A1,FIND(CHAR(171),SUBSTITUTE(A1," ",CHAR(171),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Last edited by Bobble : 07-25-2014 at 02:42 PM. Reason: Clarity and 'cause spellchecker boned me. |
07-25-2014, 02:05 PM | #9 |
Head Coach
Join Date: Dec 2002
Location: Maryland
|
Heh. I was going to suggest exporting it to text (csv), manipulating it in something like Perl, then reopening in Excel.
__________________
null |
07-25-2014, 02:20 PM | #10 |
Pro Starter
Join Date: Jan 2002
Location: Pittsburgh
|
This may be solved already, just reading it all now
Are all the records the same in that there are only 6 strings per record, regardless of string length? ETA: If yes, Bobble's answer seems to be on the right track. Have 6 columns next to each record to parse out each string into it's own cell.
__________________
"Do you guys play fast tempos with odd time signatures?" "Yeah" "Cool!!" Last edited by Suburban Rhythm : 07-25-2014 at 02:21 PM. |
07-25-2014, 08:30 PM | #11 |
College Starter
Join Date: Dec 2006
|
Is there not a separating character between title & author? Its definitely doable if there is a known character (double spaces, [space]hyphen[space], etc.) that separates title & author reliably.
Just tested this out. Works easily for a hyphen-separated scenario. If its a comma or " , " (e.g. [space]comma[space] to remove the excess spaces) just put that in the FIND part of this. =LEFT(A1;FIND("-";A1;1)-1) |
07-26-2014, 11:12 PM | #12 |
Pro Rookie
Join Date: Feb 2003
Location: Raleigh, NC
|
Also found on the web. Seems to work pretty well with no need for splitting text to columns and just relies on the text in the cell as given:
=LEFT(SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),SEARCH("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Basically, it counts out the number of spaces in the text, then subtitutes a delimiter for the last space in the string, then chops that last part beyond the delimeter. |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|