Software

Ask something
Question Locked

Will anyone help me with an Excel problem?

0 helpful
18 replies

by ron k. 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have data in a text format: c,b,a,d,e,
I want to sort that into alphabetical order in column A, ascending, each value on it's own row without having to do it manually. It's a large text file.
I can't figure out how to do it or even if it CAN be done.
If it's not too much trouble could you please help me? Knowing that it can't be done would be helpful too.

18 replies

by ron k.

I'm 53 years old and happily retired. Besides, if it was homework I'd say it was.

by purpleskys

function under editing (Excel 2007)? i'm just guessing...i haven't messed around with excel a whole lot, but most of what i've learned is current...(crossing my fingers that it's helpful :) )

by ron k.

:) I've found the Sort function but the problem is that all of the letters (representing words) are all in cell A1. Sort, by itself, sees them as one big word so it does nothing.
I think each letter in my example would be called a CSV, comma separated value.
I've found a lot about CSV using Google but I'm not even sure that's what I need.

by purpleskys

i don't know much about CSV...Darryl may have an idea but he's not around right now...i'll pick his brain in the morning or ask him to read your post...good luck in the mean time :)

by ron k.

I'll get back into reading more tomorrow to see if I can figure it out.
Here's a thumb for you just because I can give 'em.

by darryl~

All you have to do is change the extention from .txt to .csv and it will open in excel fine.

Sometimes I want to manipulate the data in the text file before bringing it into an Excel file....in those cases I import it into Access - that gives me the ability to choose which columns/fields I want and I can even change them to different formats incase I want to...for instance, I can change a text field to a date field or a number fielf to a text field.

Once I have it in Access the way I like it, I export it to a CSV file so it can be opened in Excel.

I convert a lot of different files like that quite regularly at work...we quite often get data in PDF format that we have to get into a database, that can be difficult, let me or PurpleSkys know if you run into anything like that...I can help you out ;)

by ron k.

I''m trying to wake up. I changed my text extension, put my data into Cell A! and it didn't sort; it just sat there.
I'll provide a better example and explanation of what I'm trying to do down below. I want to try Access first even though I know virtually nothing about it. Maybe I can figure it out. I'll post back.
You given me hope.

by nexs

to 'export' an excel file into a text document, columns are separated by commas.
So it only seems fair that you can import these back into excel.
is that the question? if not, i'm not sure what the question is...

by nexs

I just created a test 'txt' document with 5 columns and 2 rows.
Each column is separated by a comma and each row separated by a line break(new line).
All i did was rename the file from 'New Text Document.txt' to 'New Text Document.csv' and it opened in excel perfectly.

Hope that helps with getting your 'sort' function to work

Edited for clarity

by ron k.

I have this sort of text in an .odt, OpenOffice, document: Robertson, Grant, Applebee, Johnson,
There are 578 words/3,672 characters and they are all in one continuous 'sentence' or 'paragraph', all lumped together, simply separated by commas.
I'm trying to sort them alphabetically in a single column from A to Z with A at the top and Z at the bottom.
I tried just putting c,a,b, in cell A1 of an Office 2003 Excel spreadsheet thinking that if I could sort those few letters I'd be able to sort the pile. No luck, so far.
I have Office 2007 available if that's any better for doing what I want to do.

by ron k.

If I use 'Data\Text to columns' I get the columns. I can't go from that to having a single column. Also, while doing that I get the warning that some data will be lost because the amount of data is wider than the spreadsheet.
I may not be headed in the right direction.

by locolobo

now copy the cells, right click where you want to put the data and select "paste special" and check transpose. Try it with a smaller selection first.

by ron k.

*big sigh of relief*
Thank you. Post back several times, I'll give you more thumbs. :)

by darryl~

My wife threw that at me quickly this morning before she left for the day & I've been home sick for a few days & not thinking all that clear anyway.

I think me & NexS were of the understanding it was a "txt" file & missed the fact that it was an Open Office file.

Sometimes that copy & paste special doesn't always work the way you expect it to (sometimes it doesn't always pick up blank cells/field records....etc)...when I run into that type of thing, I usually just do a "save as" to a plain text file or do a copy & paste into a blank Notepad file and save it.....then do the conversion to a csv file.

csv files are really friendly to Excel...if you use Excel a lot, it wouldn't hurt to play with it a little...you may find it useful.

by locolobo

glad it worked. Darryl is right about paste special not always doing what you expected. That's why I suggested trying a smaller sample first.

by nexs

But i am glad the desired outcome was reached.
good job!

All the best ;)

by ron k.

It worked great. I appreciate your taking the time to look at my question.

This discussion is closed to new replies.