Welcome to NeoOffice developer notes and announcements
NeoOffice
Developer notes and announcements
 
 

This website is an archive and is no longer active
NeoOffice announcements have moved to the NeoOffice News website


Support
· Forums
· NeoOffice Support
· NeoWiki


Announcements
· Twitter @NeoOffice


Downloads
· Download NeoOffice


  
NeoOffice :: View topic - Parsing names in a spreadsheet
Parsing names in a spreadsheet
 
   NeoOffice Forum Index -> NeoOffice Releases
View previous topic :: View next topic  
Author Message
dndrich
Operator


Joined: Jan 03, 2007
Posts: 43

PostPosted: Thu Feb 01, 2007 6:38 pm    Post subject: Parsing names in a spreadsheet

Neopals:

I have a spreadsheet with names and phone numbers. The names are in one row with the first name and last name together I want to separate the first name from the last name, and have them in different cells. I know there are all kinds of nice macros that come with the program. I sort of looked them over, but didn't see one that would do this. Does anybody have any ideas on how to do this?

Daniel
Back to top
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Sat Feb 03, 2007 6:17 am    Post subject:

Hi Daniel,

You don't need a macro, just spreadsheet functions.

Firstly I'm making the assumption that all the names consist of just a first name and a surname - no middle initials or names - and name in the examples below is in cell A2.

Getting the first name is easy. Use the LEFT function in conjunction with the SEARCH function to find the space between the names and get everything to the left of it:

=LEFT(A2;SEARCH(" ";A2)-1)

Getting the surname is a little trickier. The RIGHT function doesn't take a start position (i.e. the result of SEARCH + 1), but a number of characters from the right. So you need to find out how long the name is first, using LEN.

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2))

Alternatively you can use the mid function:

=MID(A2;SEARCH(" ";A2)+1;LEN(A2))

If ALL the names have middle names or initials then you could use MID to find the middle name:

=MID(A2;SEARCH(" ";A2)+1;SEARCH(" ";A2;SEARCH(" ";A2)+1)-SEARCH(" ";A2)-1)

and again to find the surname:

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2)+1))

If you've got lots of space in your spreadsheet you can make the formulas more simple by breaking them up in to different cells by simply repeating the first two formulas.

Hope that helps - if you need a better explanation of how it works I'll try again!

Andy.
Back to top
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Sat Feb 03, 2007 7:19 am    Post subject:

A little more playing around gives the following spreadsheet which can cope with a single middle name or initial:

Code:
Full Name [Cell B12]
          =LEFT(B12;SEARCH(" ";B12)-1)
          =RIGHT(B12;LEN(B12)-SEARCH(" ";B12))
          =LEFT(B14;SEARCH(" ";B14)-1)
          =RIGHT(B14;LEN(B14)-SEARCH(" ";B14))
First     =IF(ISERR(B13);"";B13)
Middle    =IF(ISERR(B15);"";B15)
Last      =IF(ISERR(D16);IF(ISERR(D14);"";D14);D16)

HtH

Andy
Back to top
Lorinda
Captain Mifune


Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA

PostPosted: Sat Feb 03, 2007 7:48 am    Post subject:

Andy:

I've been following this thread, too, as I've wanted to do the same thing. I have a mix of names, so your last solution is of interest to me, but I'm not quite following; I presume that each of the three lines under "Full Name" Go in a cell following the full name (B13, B14, B15 respectively?)

Also, is there a way to "dump" the formula result so that it enters the actual text into the cell rather than the formula? (Once I have the names parsed, I'd like to delete the "full name" column, but I can't do that if it remains a formula)
Back to top
dndrich
Operator


Joined: Jan 03, 2007
Posts: 43

PostPosted: Sat Feb 03, 2007 9:52 am    Post subject:

Wow! You are brilliant. I will try this later today. I, too, like some of the logic puzzles presented by manipulation of spreadsheet data. I'll let you know how it goes!
Back to top
Lorinda
Captain Mifune


Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA

PostPosted: Sat Feb 03, 2007 9:54 am    Post subject:

Andy:

You can ignore my first question; I experimented and discovered my assumption was correct.

I also figured out a way to "recombine" the first and middle names:

Code:
=IF(ISERR(B15);(B17);CONCATENATE(T(B17);" ";T(B18)))


That assumes I got the cell names right when I shift from my spreadsheet (where I'm working horitizontally, not vertically) to your example.

I would still be interested in the possibility of "dumping" the results, both for the reasons stated and because I have some non-standard entries that I'm going to have to enter as text rather than the formula. (either that, or add even more complicated if/then statements). I have discovered, though, how to hide cells, which helps considerably.

I've added this thread to the "threads to wiki" list in the wiki forum, as I think this would be a good one to have there.
Back to top
sardisson
Town Crier
Town Crier


Joined: Feb 01, 2004
Posts: 4588

PostPosted: Sat Feb 03, 2007 12:32 pm    Post subject:

Lorinda wrote:
I've added this thread to the "threads to wiki" list in the wiki forum, as I think this would be a good one to have there.

Indeed!

I always end up dumping stuff into a table in Writer and then going back to text to manipulate things, but this sounds like far fewer steps Smile

Smokey

_________________
"[...] whether the duck drinks hot chocolate or coffee is irrelevant." -- ovvldc and sardisson in the NeoWiki
Back to top
Lorinda
Captain Mifune


Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA

PostPosted: Sat Feb 03, 2007 12:47 pm    Post subject:

I've figured out at least one way to provide the "text dump" I was looking for; After first saving the file as a calc file, I saved the sheet as a CSV file; then I reopened the Calc document, and inserted the CSV file as a new sheet. I did have to go through and wipe out all the columns I used for the parsing calculations, but that was easy to do.

I also figured out how to nest IF statements to deal with the remaining parsing issues I had specific to my sheet. I'm very glad for that, because I have 2695 rows of data to work with!
Back to top
GeorgeJ
Blue Pill


Joined: Oct 09, 2004
Posts: 3

PostPosted: Wed Feb 28, 2007 5:11 pm    Post subject: Text dump via Paste Special

Don't know if this is the kind of "text dump" you are looking for:

Copy the cells containing the formulas to the clipboard.
From the Edit menu, choose Paste Special. In the Selection part, deactivate the Formulas option. Choose OK.

In my experience, this will paste the text that the formulas produced as pure text.

George
Back to top
Lorinda
Captain Mifune


Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA

PostPosted: Wed Feb 28, 2007 5:35 pm    Post subject:

Thanks! I'll give it a try when I have a chance. And it's a good tip to include in the wiki article, which has yet to get written. (So much wiki to add, so little time... Smile )
Back to top
LemonAid
The Anomaly


Joined: Nov 21, 2005
Posts: 1285
Location: Witless Protection Program

PostPosted: Wed Feb 28, 2007 7:04 pm    Post subject:

"Wikki don't lose that number" Cool

Philip (See ... again, NeoOffice makes you smarter! Wink )
Back to top
Display posts from previous:   
   NeoOffice Forum Index -> NeoOffice Releases All times are GMT - 7 Hours
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Powered by phpBB © 2001, 2005 phpBB Group

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest © Planamesa Inc.
NeoOffice is a registered trademark of Planamesa Inc. and may not be used without permission.
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.