Text to Columns Problem

[Miki]

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

 

[Teethless mama]

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

 

[T. Valko]

Try one of these.

Create this defined name
Goto the Formulas tab>Defined names>Define 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.

 

[T. Valko]

=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"

 

[T. Valko]

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.