On Thu, 26 Jan 2006 15:22:02 -0800, NewKid
wrote:

I am trying to split names across columns. The problem is that some names
spilt into 3 columns (first, middle, last), and others split across 5 or 6
(extra names, etc.)

Is there a way to specify split, using SPACE as a delimiter, but only split
on the first TWO spaces, then leave the rest alone?

I'm having a terrible time figuring this out. Any ideas anyone?

Thank you so much!

Mary

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use these formulas:

Assume Name String is in A1:

B1: =REGEX.MID($A1,"\w+",1)
C1: =REGEX.MID($A1,"\w+",2)
D1: =REGEX.SUBSTITUTE(TRIM(A1),"^\w+(\s|$)(\w+(\s|$))? ")

These are "regular expressions"

B1: Get first word
C1: Get 2nd word
D1: Removes 1st and (if present) 2nd words; so returns the rest
--ron