Excel Help

[Barkeep49]

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?

[henry296]

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.

[SteveMax58]

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 "-").

[Butter]

Originally Posted by henry296

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.

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.

[Barkeep49]

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...

[Butter]

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.

[Barkeep49]

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.

[Bobble]

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)

[cuervo72]

Heh. I was going to suggest exporting it to text (csv), manipulating it in something like Perl, then reopening in Excel.

[Suburban Rhythm]

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.

[SteveMax58]

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)

[Wolfpack]

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.