formatting weird data

Discuss the spreadsheet application
Post Reply
Bobert
Posts: 3
Joined: Sun Dec 16, 2007 8:00 pm

formatting weird data

Post by Bobert »

i have a spreadsheet with names, phone numbers, email addresses, and one character flags (F,D,M,P,A, R or T). the problem is that it's all jumbled. the names are for the most part in the first column, the rest of it is all over the place and some of rows contain nothing at all. i don't really need the phone numbers or email addresses, just each name and corresponding flag (one flag per person). what i want to do is delete all empty rows, all phone numbers all email addresses and put all of the names in one column and all the flags in one other, so that i can sort the list of names according to the flags (e.g. first Ms then Ps). i tried all sorts of IFs and ISCHARs and got nothing. this doesn't seem like it should be such a hard thing, but i couldn't figure it out. any ideas?
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: formatting weird data

Post by Hagar Delest »

Difficult to say without seeing how it looks like. Can you replace data by dummy data but keeping the same places and upload such a spreadsheet here ?
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Bobert
Posts: 3
Joined: Sun Dec 16, 2007 8:00 pm

Re: formatting weird data

Post by Bobert »

i'll see if i can make some up. but in the meantime, i'd just like to know how to do the following basic operations in a selected range:
1. delete or empty cells containing numbers or the @ symbol
2. delete empty cells
3. move cells that contain only one character into one column

though i'd prefer to know how to do this properly, those operations should format enough of the spreadsheet that i'll be able to do the rest manually.
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: formatting weird data

Post by Hagar Delest »

Macros can do that.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Bobert
Posts: 3
Joined: Sun Dec 16, 2007 8:00 pm

Re: formatting weird data

Post by Bobert »

i prefer not to use macros, and besides, this shouldn't be that hard.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: formatting weird data

Post by acknak »

You're right: it's not that hard--for a human, who knows what addresses are supposed to look like, what names are supposed to look like, etc. But it is very difficult to formulate rules that will allow a computer to "clean up a mess" reasonably accurately. Even the better tools that OOo provides (e.g. Search & Replace using text patterns) are complex and take some time to learn to use effectively.

You really are likely to do best by just doing it "by hand"--copy & paste the parts you want into the columns you want--unless you have many hundreds of rows to process.
AOO4/LO5 • Linux • Fedora 23
Post Reply