Page 1 of 1
splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 3:25 am
by xkmail
I have a spreadsheet where A1 is Contact, A2 is Bill Smith.
I want A2 to Be Bill, B2 to be Smith and so on.
Kevin
Re: splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 4:18 am
by acknak
Select your data, then Data > Text to Columns.
By changing the delimiter character to 'space', you can split the two names into two separate columns.
Have you read the
Forum Survival Guide?
What OS and OOo version are you using?
You must have OOo >= 2.4.0 to use the "Text to Columns" feature.
Re: splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 3:46 pm
by Duhhh
If you need to do it with formulas, you could use:
A2: Bill Smith
B2: =LEFT(A2;SEARCH(" ";A2)-1)
C2: =MID(A2;SEARCH(" ";A2)+1;LEN(A2))
Re: splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 11:21 pm
by xkmail
I don't have data> text to colums.
Is there a keyboard shortcut or where can I add it in.
remember this is calc not writer.
Re: splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 11:24 pm
by xkmail
Duhhh wrote:If you need to do it with formulas, you could use:
A2: Bill Smith
B2: =LEFT(A2;SEARCH(" ";A2)-1)
C2: =MID(A2;SEARCH(" ";A2)+1;LEN(A2))
Yeah that worked great I inserted two columns to the right of "Contact" column and ran formulas.
Then dragged down to repeat all names.
Out of curiosity is there a way I can drag a forumula from cell C2 to C3:C555 ?
Re: splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 11:28 pm
by xkmail
Now how do I delete Column A without making "VALUE" show up in my new columns B-First Name C-Last Name
Re: splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 11:29 pm
by xkmail
acknak wrote:Select your data, then Data > Text to Columns.
By changing the delimiter character to 'space', you can split the two names into two separate columns.
Have you read the
Forum Survival Guide?
What OS and OOo version are you using?
You must have OOo >= 2.4.0 to use the "Text to Columns" feature.
Vista 2.3
Guess I need the update for text to columns
Re: splitting contact into first name last name colums
Posted: Sun Jun 08, 2008 11:44 pm
by Villeroy
Guess I need the update for text to columns
You can also use almost the same feature in Writer.
Copy the cells, open a new Writer doc, paste special (Ctrl+Shift+V) as "unformatted text", Menu:Table>Convert>Text to columns...
Out of curiosity is there a way I can drag a forumula from cell C2 to C3:C555
You could try to double-click the tiny-square at selected C2. This should copy down until the end of the adjacent list. Check with Ctrl+End (goto last cell).
If the does not work for some reason: Type C2:C555 into the name box (left of formula bar), Menu:Edit>Fill>Down
Re: splitting contact into first name last name colums
Posted: Mon Jun 09, 2008 12:59 am
by Dave
Windows? If a text file, label as ".CSV". If a spreadsheet, save as a CSV. Then download freeware "CSVed" to do the job easily.
David.
Re: splitting contact into first name last name colums
Posted: Mon Jun 09, 2008 6:30 pm
by Duhhh
xkmail wrote:Now how do I delete Column A without making "VALUE" show up in my new columns B-First Name C-Last Name
One way is to just HIDE column A. The other way is to select colums B & C, COPY, then PASTE SPECIAL and de-select formulas. This will convert everything in column B & C to plain text, and you can then delete column A.