1

I have an Excel spreadsheet (.xlsx) with multiple rows and columns of cells containing space-separated text. For example: 

A1  the good of the many
B2  all dogs go to heaven
C3  eat my shorts

I would like to convert this to:

  • a text (.txt) file
  • with one cell per line
  • alphabetized by the first letter of the cell
  • no spaces (or other diacritical marks, just the letters), within the text.

So the text file would come out as:

alldogsgotoheaven
eatmyshorts
thegoodofthemany.

Can anyone help me with this?

Improve this question
2
  • Only three columns of data??
    – Gary's Student
    Jan 31, 2015 at 16:43
  • Paste the data into a text file, replace all spaces with nothing and replace all tabs with spaces.
    – user387876
    Jan 31, 2015 at 17:28

1 Answer 1

Reset to default
0

Andrew Roth, if you're still monitoring this thread, here is a formula I use in a spreadsheet that contains my movie collection. I use the column that contains these formulas a sort helper so that when I sort, Excel ignores: "the", spaces, commas, semicolons, periods, and considers an ampersand as the word "and".

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@[Strip Parens]],"The ","",1),",","")," ",""),".",""),"&","and"),":",""),"'","")

My spreadsheet is formatted as a table, so "[@[Strip Parens]]" refers to the column name. This is the part of the Substitute function that would normally contain the cell reference. You can replace my stuff with whatever diacritical marks you want to eliminate.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .