I have 1 column with a persons full name, and I want to split their first and last names into 2 separate columns. How2?

an easy one for the wise mr googs

http://www.uwec.edu/help/Excel03/ws-split.htm

http://memimage.cardomain.net/member_images/8/web/500000-500999/500536_29.jpg

trillions link works if it's a delimited file. Easiest way of doing it is to make a copy of the whole range/column, then do a find & replace of *_ to get rid of everything before the space and then reverse that for the second column with _* to get rid of everything after the space. Oh, in case you don't get it.. the "_" is just an indicator for the space.

Milage may vary with names containing more than one space between them

Need more details.
Text to columns is the answer, how much prepping you need to do on the data before doing it is the trick.

Cos names Can be stored in many ways.

Smith, John
Smith, John J
John J Smith
SMITH John
John SMITH
John Smith

or just:

obes

regex in excel

why oh why doesn't even Excel 2007 natively support regex?

Excel is aimed at normal office people. Regex to normal office people is some nerd version of swahili.

And it is available as a add-in from MS I believe.

so you admit you are a nerd then obes?

even making your own functions in excel isn't as straightforward as it could be

it's time they forgot the office people and targeted the leet hax0rs

so has anyone answered my question yet? All I see is links to s*** that doesn't help me (although I could be wrong as I am dumb).

Names are sorted like this:

Simon Casagrande
Sean O'Brien

etc

steamyhawtmen.xls?

Export as CSV, open in notepad, find replace "space" with "," and open up in Excel again. Mind you it will quite plainly f*** up for all those people that have more than 2 words in their name but it's probably something you could fix manually.

There we go, so easy, thanks heaps ticman

casa when you go number twosey, does it hurt your brain as it scrapes past?

find replace "space"

What the f*** did I say????

Told you how to fix it n00b

Ladies and Gentlemen, although I'm the proud winner of the Casa Excel Tricky Question trophy I would like to share it with Hermi.

This thread is just like Blades of Glory.

Hermi and Ticman will now perform their ice dance routine.

Ticman's solution is retarded. Casa you are an office bitch, learn to use Office bitch programs!

This is the in program version of Ticman's solution.

Select the column after the names
(menu) Insert -> Column
(menu) Data -> Text to Column
!Wizard!
Select Delimited
Next
Untick Tab
Tick Space (or whatever it is you have as a delimiter, can be multiple things)
Next
f*** with Formatting
Finish.
*IF* it says about to replace the contents with destination cells. Press Cancel.
Then at least 1 of the names did no comply to the formatting you suggested.
Which is when you need to write a formula.

Now for the reliable (depending on how f***ed up your data is) formula way.

nb. using <pre> which probably doesn't work.

Using A9 as the example cell.

To find out how words are in a cell

=IF(LEN(TRIM(A9))=0,0,LEN(A9)-LEN(SUBSTITUTE(A9," ",""))+1)

That will even pick up double spaces in between words because of the trim function.

To return the Last Word in a cell.

=MID(SUBSTITUTE(TRIM(A9)," ","^",LEN(TRIM(A9))-LEN(SUBSTITUTE(TRIM(A9)," ",""))),FIND("^",SUBSTITUTE(TRIM(A9)," ","^",LEN(TRIM(A9))-LEN(SUBSTITUTE(TRIM(A9)," ",""))))+1,256)

To Return the First Word.

=LEFT(TRIM(A9),FIND(" ",TRIM(A9))-1)

You could then use use substitute to remove extra formating, and functions like Proper to make em look consistent.

eg.

=PROPER(SUBSTITUTE(LEFT(TRIM(A9),FIND(" ",TRIM(A9))-1),",",""))

eg. remove commas and proper case it.

This thread is full of gay, thanks everyone, your trophies are in the mail.

I'll take my retarded solution I came up with in 30 seconds compared to the insane solution Obes proposes.

Trophy is still mine biotch!

For your solution to have been f***ed. All it needed was a name to have a , in it. Or for a Billy Bob Red-neck jnr to be in the mailing list. And you had no way to check if the data was f***ed.

Excel (and OOO Calc) have some really cool functionality in them and far too many people who use them daily, use it like its First Choice (pro 80s spec DOS works package).

Turns out all he wanted was the first name.

=PROPER(LEFT(TRIM(A9),FIND(" ",TRIM(A9))-1))

Why do it using a formula ?

Cos if you have to do it regularly, or via data pulled out via a query. It can all be automated, and made into a button so MrManager type can do it with out bothering you. You can also build in idiot proofing and error checking.

ps. A Filemaker database that was designed in Filemaker II on an Apple, pre any relational versions, and then have had 10 people over at least 15years all with limited computer experience all randomly enter data in different ways. Has taught me a lot about how to get excel to tidy crap data up.

Crazy s*** like
phone number: 3865 ???? ... wtf ? why bother entering it
and dates : 1-2, Jan-2, 2-Jan, 1/2/97 (and this data covers 1891-2007 so was that 1897 or 1997 ?) entered on machines set in both US and Aus formats ...
I think it would have to be the worst set of data I have ever seen.
No constraints, No type checking, no format enforcement, There are even records with no name for the person ....

So angor ...

Just do it manually you lazy f***

16000 rows, by 110 columns in 1 huge flat file exported out of Filemaker...
Which I have to mangle into a relational DB that's on Informix.

sif. manual

btw. the users are still editing stuff in Filemaker, so I have to write a series of scripts that mangle the data.

I did it once, they kept using Filemaker instead.
Work asked me to do it again, to which I replied its been done get them to pay for a pro to do it... 16 grand latter they made sure it failed by continuing to enter data while it was happening, and given us "old data" and then saying they didn't need stuff imported that they did. Now they have been told do it ! ... and they have come back to me again ... So this time I'll write scripts so I can rerun the process as many times as needed.

force data validation on the cells = win

Anyone know how to change the colour of a cell in an IF statement??

=IF(logic, [colour=red]&"OMG BAD", [colour=blue]&"ITS ALL GOOD")

sorta thing.

just use conditional formatting?

save to text file, bust out perl, profit

I'd say conditional formatting also.

Which makes heardy not a noob ... this thread sucks now

haha that means I know 'something'

jesus, who'd have thought ;)

I am no excel guru by any means, but I have to use it for a f***load of things at work and have picked up a thing or two over the years

<3 obes

save to text file, bust out perl, profit

Welcome to the 90s!

=D

im still living in the 90's man

can perl do mail loops?

perl sux

sif perl sux

#!/usr/bin/perl -w                                      # camel code

use strict;



                                           $_='ev

                                       al("seek\040D

           ATA,0,                  0;");foreach(1..3)

       {;}my               @camel1hump;my$camel;

  my$Camel  ;while(             ){$_=sprintf("%-6

9s",$_);my@dromedary           1=split(//);if(defined($

_=)){@camel1hum        p=split(//);}while(@dromeda

 ry1){my$camel1hump=0      ;my$CAMEL=3;if(defined($_=shif

        t(@dromedary1    ))&&/\S/){$camel1hump+=1<<$CAMEL;}

       $CAMEL--;if(d   efined($_=shift(@dromedary1))&&/\S/){

      $camel1hump+=1  <<$CAMEL;}$CAMEL--;if(defined($_=shift(

     @camel1hump))&&/\S/){$camel1hump+=1<<$CAMEL;}$CAMEL--;if(

     defined($_=shift(@camel1hump))&&/\S/){$camel1hump+=1<<$CAME

     L;;}$camel.=(split(//,"\040..m`{/J\047\134}L^7FX"))[$camel1h

      ump];}$camel.="\n";}@camel1hump=split(/\n/,$camel);foreach(@

      camel1hump){chomp;$Camel=$_;y/LJF7\173\175`\047/\061\062\063\

      064\065\066\067\070/;y/12345678/JL7F\175\173\047`/;$_=reverse;

       print"$_\040$Camel\n";}foreach(@camel1hump){chomp;$Camel=$_;y

        /LJF7\173\175`\047/12345678/;y/12345678/JL7F\175\173\0 47`/;

         $_=reverse;print"\040$_$Camel\n";}';;s/\s*//g;;eval;   eval

           ("seek\040DATA,0,0;");undef$/;$_=;s/\s*//g;(   );;s

             ;^.*_;;;map{eval"print\"$_\"";}/.{4}/g; __DATA__   \124

               \1   50\145\040\165\163\145\040\157\1 46\040\1  41\0

                    40\143\141  \155\145\1 54\040\1   51\155\  141

                    \147\145\0  40\151\156 \040\141    \163\16 3\

                     157\143\   151\141\16  4\151\1     57\156

                     \040\167  \151\164\1   50\040\      120\1

                     45\162\   154\040\15    1\163\      040\14

                     1\040\1   64\162\1      41\144       \145\

                     155\14    1\162\       153\04        0\157

                      \146\     040\11     7\047\         122\1

                      45\15      1\154\1  54\171          \040

                      \046\         012\101\16            3\16

                      3\15           7\143\15             1\14

                      1\16            4\145\163           \054

                     \040            \111\156\14         3\056

                    \040\         125\163\145\14         4\040\

                    167\1        51\164\1  50\0         40\160\

                  145\162                              \155\151

                \163\163                                \151\1

              57\156\056

last edited by Spook at 09:03:09 29/Jun/07

opec catches a spook