formatting weird data
formatting weird data
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?
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: formatting weird data
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
Re: formatting weird data
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.
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.
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: formatting weird data
Macros can do that.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: formatting weird data
i prefer not to use macros, and besides, this shouldn't be that hard.
Re: formatting weird data
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.
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