Text to Columns Problem
 

Hi,

I am using Office 2K7 and facing a problem in splitting the text into
different columns.

I have lastnames and firstnames together in a cell and there is no such
delimiter between them which can be identified by a single way that the last
name starts with a Upper letter only.

e.g. CandyRachel . I need to separate it into two different columns as
'Candy' and 'Rachel'.

I think this is possible using Char function but not able to find out the way.

Any help will be appreciated..

Thanks in advance..

Miki

Text to Columns Problem
 

Assuming your data in A1 and down

In C1:
=MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))=65)* (CODE(MID(A1,ROW($2:$99),1))<90),)+1,99)

ctrl+shift+enter, not just enter


In B1: =SUBSTITUTE(A1,C1,)
Just preess enter



"Miki" wrote:

Hi,

I am using Office 2K7 and facing a problem in splitting the text into
different columns.

I have lastnames and firstnames together in a cell and there is no such
delimiter between them which can be identified by a single way that the last
name starts with a Upper letter only.

e.g. CandyRachel . I need to separate it into two different columns as
'Candy' and 'Rachel'.

I think this is possible using Char function but not able to find out the way.

Any help will be appreciated..

Thanks in advance..

Miki

Text to Columns Problem
 

Try one of these.

Create this defined name
Goto the Formulas tabDefined namesDefine name
Name: Letters
Refers to:

="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z"

OK out

Note that the underscores are intentional and the letters need to be in
uppercase.

A2 = CandyRachel

Here's the short formula version**.

Entered in C2 to extract the last name.

=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20)

Entered in B2 to extract the first name:

=SUBSTITUTE(A2,C2,"")

Here's the long formula version** to extract the last name:

=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1),Letters ),ROW(INDIRECT("2:"&LEN(A2)))),20)

** the difference in the short and long formulas is that the long version is
robust against row insertions in certain situations. If you know that you
will never need to insert new rows at the top of the sheet then use the
short version.

When using an expression like:

ROW(A$2:A$50)

This expression is vulnerable to row insertions which could lead the formula
to fail. For example, if you inserted a new row 1 then that expression will
change to reflect the row insertion. Instead of:

ROW(A$2:A$50)

It will change to:

ROW(A$3:A$51)

We're using this expression in the formula to define the starting point at
which to look for the an uppercase letter. So, inserting new rows could
shift that starting point and lead the formula to fail.

For this reason I would use the long formula which is robust against
(accounts for) such row insertions. The downside of using the long version
is that it's a bit longer and it uses the volatile function INDIRECT which
means that this (these) formulas will recalculate *every* time a calculation
takes place. If you have "lots" of volatile functions it could possibly slow
things down.

--
Biff
Microsoft Excel MVP


"Miki" wrote in message
...
Hi,

I am using Office 2K7 and facing a problem in splitting the text into
different columns.

I have lastnames and firstnames together in a cell and there is no such
delimiter between them which can be identified by a single way that the
last
name starts with a Upper letter only.

e.g. CandyRachel . I need to separate it into two different columns as
'Candy' and 'Rachel'.

I think this is possible using Char function but not able to find out the
way.

Any help will be appreciated..

Thanks in advance..

Miki

Text to Columns Problem
 

=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20)

Found a bug in that formula when the last name starts with the letter A. To
correct it change the defined named string from:

="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z"

To:

="_ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z"

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try one of these.

Create this defined name
Goto the Formulas tabDefined namesDefine name
Name: Letters
Refers to:

="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z"

OK out

Note that the underscores are intentional and the letters need to be in
uppercase.

A2 = CandyRachel

Here's the short formula version**.

Entered in C2 to extract the last name.

=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20)

Entered in B2 to extract the first name:

=SUBSTITUTE(A2,C2,"")

Here's the long formula version** to extract the last name:

=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1),Letters ),ROW(INDIRECT("2:"&LEN(A2)))),20)

** the difference in the short and long formulas is that the long version
is robust against row insertions in certain situations. If you know that
you will never need to insert new rows at the top of the sheet then use
the short version.

When using an expression like:

ROW(A$2:A$50)

This expression is vulnerable to row insertions which could lead the
formula to fail. For example, if you inserted a new row 1 then that
expression will change to reflect the row insertion. Instead of:

ROW(A$2:A$50)

It will change to:

ROW(A$3:A$51)

We're using this expression in the formula to define the starting point at
which to look for the an uppercase letter. So, inserting new rows could
shift that starting point and lead the formula to fail.

For this reason I would use the long formula which is robust against
(accounts for) such row insertions. The downside of using the long version
is that it's a bit longer and it uses the volatile function INDIRECT which
means that this (these) formulas will recalculate *every* time a
calculation takes place. If you have "lots" of volatile functions it could
possibly slow things down.

--
Biff
Microsoft Excel MVP


"Miki" wrote in message
...
Hi,

I am using Office 2K7 and facing a problem in splitting the text into
different columns.

I have lastnames and firstnames together in a cell and there is no such
delimiter between them which can be identified by a single way that the
last
name starts with a Upper letter only.

e.g. CandyRachel . I need to separate it into two different columns as
'Candy' and 'Rachel'.

I think this is possible using Char function but not able to find out the
way.

Any help will be appreciated..

Thanks in advance..

Miki

Text to Columns Problem
 

Here's another one (2 versions) that *does not* need the named string.

These are array formulas**.

=MID(A2,MATCH(1,--(ABS(CODE(MID(A2,ROW(A$2:A$50),1))-77.5)<13),0)+1,20)

=MID(A2,MATCH(1,--(ABS(CODE(MID(A2,ROW(INDIRECT("2:50")),1))-77.5)<13),0)+1,20)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20)

Found a bug in that formula when the last name starts with the letter A.
To correct it change the defined named string from:

="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z"

To:

="_ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z"

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try one of these.

Create this defined name
Goto the Formulas tabDefined namesDefine name
Name: Letters
Refers to:

="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z"

OK out

Note that the underscores are intentional and the letters need to be in
uppercase.

A2 = CandyRachel

Here's the short formula version**.

Entered in C2 to extract the last name.

=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20)

Entered in B2 to extract the first name:

=SUBSTITUTE(A2,C2,"")

Here's the long formula version** to extract the last name:

=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1),Letters ),ROW(INDIRECT("2:"&LEN(A2)))),20)

** the difference in the short and long formulas is that the long version
is robust against row insertions in certain situations. If you know that
you will never need to insert new rows at the top of the sheet then use
the short version.

When using an expression like:

ROW(A$2:A$50)

This expression is vulnerable to row insertions which could lead the
formula to fail. For example, if you inserted a new row 1 then that
expression will change to reflect the row insertion. Instead of:

ROW(A$2:A$50)

It will change to:

ROW(A$3:A$51)

We're using this expression in the formula to define the starting point
at which to look for the an uppercase letter. So, inserting new rows
could shift that starting point and lead the formula to fail.

For this reason I would use the long formula which is robust against
(accounts for) such row insertions. The downside of using the long
version is that it's a bit longer and it uses the volatile function
INDIRECT which means that this (these) formulas will recalculate *every*
time a calculation takes place. If you have "lots" of volatile functions
it could possibly slow things down.

--
Biff
Microsoft Excel MVP


"Miki" wrote in message
...
Hi,

I am using Office 2K7 and facing a problem in splitting the text into
different columns.

I have lastnames and firstnames together in a cell and there is no such
delimiter between them which can be identified by a single way that the
last
name starts with a Upper letter only.

e.g. CandyRachel . I need to separate it into two different columns as
'Candy' and 'Rachel'.

I think this is possible using Char function but not able to find out
the way.

Any help will be appreciated..

Thanks in advance..

Miki