formatting weird data

Discuss the spreadsheet application

formatting weird data

Postby Bobert » Sun Dec 16, 2007 8:14 pm

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?
Bobert
 
Posts: 3
Joined: Sun Dec 16, 2007 8:00 pm

Re: formatting weird data

Postby Hagar Delest » Sun Dec 16, 2007 11:12 pm

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 ?
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28561
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: formatting weird data

Postby Bobert » Mon Dec 17, 2007 6:31 pm

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.
Bobert
 
Posts: 3
Joined: Sun Dec 16, 2007 8:00 pm

Re: formatting weird data

Postby Hagar Delest » Mon Dec 17, 2007 7:05 pm

Macros can do that.
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28561
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: formatting weird data

Postby Bobert » Wed Dec 19, 2007 4:21 am

i prefer not to use macros, and besides, this shouldn't be that hard.
Bobert
 
Posts: 3
Joined: Sun Dec 16, 2007 8:00 pm

Re: formatting weird data

Postby acknak » Wed Dec 19, 2007 6:35 am

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
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests