I am in need of a solution to split a paste string into multiple cells within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so the string
length varies from 7 and 8 entries (seperated by commas). But there will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

Sorry got home from work and realized I made a mistake in this description...
I need the paste string to be distributed accross a row not down a column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple cells within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so the string
length varies from 7 and 8 entries (seperated by commas). But there will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

Luke

I would split these using DataText to columns.... using space as the
delimiter. This will split them into the 7/8 fields. Then you can
re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can re-assemble them
in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the helper columns
and EditPaste Special...Values to themselves to kill the formulae and
delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Luke Slotwinski" wrote in
message ...
Sorry got home from work and realized I made a mistake in this
description...
I need the paste string to be distributed accross a row not down a
column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple cells
within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so the
string
length varies from 7 and 8 entries (seperated by commas). But there will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

Nick:

Thank you for your help... I am running into a problem though.
If I set the Text to Columns for an 8 entry string format...
M1234 1112223 SHMOE, JOE ADAM 2Y(01/01/2005) M ER
then I have an entry w/o a middle name that is 7 entries long
T2323 5551212 EXAMPLE, GIRL 3Y(01/01/2004) F AUC
It will paste 3Y(01/01/2004) into E* when I'd like to have it in F*... is
there a way to limit column E* to alpha only and if there is numerics to skip
to colum F and paste?

Thank you,
Luke Slotwinski
"Nick Hodge" wrote:

Luke

I would split these using DataText to columns.... using space as the
delimiter. This will split them into the 7/8 fields. Then you can
re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can re-assemble them
in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the helper columns
and EditPaste Special...Values to themselves to kill the formulae and
delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Luke Slotwinski" wrote in
message ...
Sorry got home from work and realized I made a mistake in this
description...
I need the paste string to be distributed accross a row not down a
column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple cells
within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so the
string
length varies from 7 and 8 entries (seperated by commas). But there will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

One of those fascinating but oh so frustrating tasks unless you can trust
the original data to be consistent! I just played around for a few minutes
and came up with:
With your string in column A, Text to Columns fixed width could give you
M1234 in A, 1112223 in B and the rest in C. Then Text to Columns again on C,
this time with comma as your delimiter will put the surname in C and the
rest in D. Text to Columns again on D with ( as the delimiter leaves the
forename(s)and age in D and the rest in E. Now insert a couple of columns
between D and E. In E, put in a formula like
=IF(ISERROR(FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)),LEFT(TRIM(D1),FIND("
",TRIM(D1))),LEFT(TRIM(D1),FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)))
which should give you one or two forenames. In F, =trim(RIGHT(D1,4)) will
put the age there. Then Text to Columns on what's left with both space and )
as delimiters gives you the rest. If in stage 3 of the Text to Columns
wizard you mark the left-hand column as "Date", your date-like data will
turn into a date. All that's left then is to delete column D, and then
concatenate as you will into columns further right.

Please try this on a copy of your data first, and remember that the long
formula will only deal with one or two forenames (not three) and the little
formula in F assumes that none of your patients is over 99.

--
Noel


"Luke Slotwinski" wrote in
message ...
Nick:

Thank you for your help... I am running into a problem though.
If I set the Text to Columns for an 8 entry string format...
M1234 1112223 SHMOE, JOE ADAM 2Y(01/01/2005) M ER
then I have an entry w/o a middle name that is 7 entries long
T2323 5551212 EXAMPLE, GIRL 3Y(01/01/2004) F AUC
It will paste 3Y(01/01/2004) into E* when I'd like to have it in F*... is
there a way to limit column E* to alpha only and if there is numerics to
skip
to colum F and paste?

Thank you,
Luke Slotwinski
"Nick Hodge" wrote:

Luke

I would split these using DataText to columns.... using space as the
delimiter. This will split them into the 7/8 fields. Then you can
re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can re-assemble
them
in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the helper
columns
and EditPaste Special...Values to themselves to kill the formulae and
delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Luke Slotwinski" wrote in
message ...
Sorry got home from work and realized I made a mistake in this
description...
I need the paste string to be distributed accross a row not down a
column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple cells
within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so the
string
length varies from 7 and 8 entries (seperated by commas). But there
will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it
would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

Ildhund:

Thank you so much for your help. Sorry to keep complicating matters but the
first two entries arent always the same character count... It could be
M1-M19999 and the second entry 1-9 characxters long... kind of ruins the
fixed length delimiter.

Any thoughts?
Luke Slotwinski

"Ildhund" wrote:

One of those fascinating but oh so frustrating tasks unless you can trust
the original data to be consistent! I just played around for a few minutes
and came up with:
With your string in column A, Text to Columns fixed width could give you
M1234 in A, 1112223 in B and the rest in C. Then Text to Columns again on C,
this time with comma as your delimiter will put the surname in C and the
rest in D. Text to Columns again on D with ( as the delimiter leaves the
forename(s)and age in D and the rest in E. Now insert a couple of columns
between D and E. In E, put in a formula like
=IF(ISERROR(FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)),LEFT(TRIM(D1),FIND("
",TRIM(D1))),LEFT(TRIM(D1),FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)))
which should give you one or two forenames. In F, =trim(RIGHT(D1,4)) will
put the age there. Then Text to Columns on what's left with both space and )
as delimiters gives you the rest. If in stage 3 of the Text to Columns
wizard you mark the left-hand column as "Date", your date-like data will
turn into a date. All that's left then is to delete column D, and then
concatenate as you will into columns further right.

Please try this on a copy of your data first, and remember that the long
formula will only deal with one or two forenames (not three) and the little
formula in F assumes that none of your patients is over 99.

--
Noel


"Luke Slotwinski" wrote in
message ...
Nick:

Thank you for your help... I am running into a problem though.
If I set the Text to Columns for an 8 entry string format...
M1234 1112223 SHMOE, JOE ADAM 2Y(01/01/2005) M ER
then I have an entry w/o a middle name that is 7 entries long
T2323 5551212 EXAMPLE, GIRL 3Y(01/01/2004) F AUC
It will paste 3Y(01/01/2004) into E* when I'd like to have it in F*... is
there a way to limit column E* to alpha only and if there is numerics to
skip
to colum F and paste?

Thank you,
Luke Slotwinski
"Nick Hodge" wrote:

Luke

I would split these using DataText to columns.... using space as the
delimiter. This will split them into the 7/8 fields. Then you can
re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can re-assemble
them
in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the helper
columns
and EditPaste Special...Values to themselves to kill the formulae and
delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Luke Slotwinski" wrote in
message ...
Sorry got home from work and realized I made a mistake in this
description...
I need the paste string to be distributed accross a row not down a
column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple cells
within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so the
string
length varies from 7 and 8 entries (seperated by commas). But there
will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it
would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

See what I mean about frustrating? Try again:

*On a copy of your data*:

(i) With your strings in column A, select the data and then pick Text to
Columns delimited with space and comma as the delimiter. In step 3, mark all
but the first three columns "Do not import column (skip)" and change the
destination from $A$1 to $B$1. This should give you M1234 in B1, 1112223 in
C1 and SHMOE in D1.
(ii) Pick Text to Columns delimited again. This time, make your delimiter
Other, "(". In step 3, mark the first column "Do not import..." and change
the destination to $G$1. You should now have 01/01/2005) M ER in G1.
(iii) Select column G, pick Text to Columns fixed width. At step 2, click to
the left of the ")" to create an extra column containing just ")". At step
3, mark the first column "Date" and select your format from the dropdown,
Mark the ")" column "Do not import..." and Finish. You should now have
01/01/2005 in G1, M in H1 and ER in I1.
(iii) In E1, put =TRIM(MID(A1,FIND(",",A1)+2,FIND("(",A1)-FIND(",",A1)-5))
You should now have one or two forenames in E. Copy the formula down as far
as you need to go (eg. by double clicking on the little black square in the
bottom right-hand corner of E1 when it is the active cell).
(iv) In F1, put =VALUE(MID(A1,FIND("(",A1)-3,2)). A one- or two-digit age
should result. Copy the formula down.

This takes much longer to write out than it does to perform, and is
submitted with every conceivable caveat. If you end up with a table you
think you can use, the last stage before concatenating would be to select
the whole table, copy and then paste special values (to get rid of the
formulae). And a bit of advice from one who's often been there and done
that: forget about the concatenation - leave the data separated by columns.
It will be so much easier to search and sort and filter, which you (or
someone else) will probably want to do sometime. You can always consolidate
data from columns when you get round to producing a printed report.

Whew!

--
Noel



"Luke Slotwinski" wrote in
message ...
Ildhund:

Thank you so much for your help. Sorry to keep complicating matters but
the
first two entries arent always the same character count... It could be
M1-M19999 and the second entry 1-9 characxters long... kind of ruins the
fixed length delimiter.

Any thoughts?
Luke Slotwinski

"Ildhund" wrote:

One of those fascinating but oh so frustrating tasks unless you can trust
the original data to be consistent! I just played around for a few
minutes
and came up with:
With your string in column A, Text to Columns fixed width could give you
M1234 in A, 1112223 in B and the rest in C. Then Text to Columns again on
C,
this time with comma as your delimiter will put the surname in C and the
rest in D. Text to Columns again on D with ( as the delimiter leaves the
forename(s)and age in D and the rest in E. Now insert a couple of columns
between D and E. In E, put in a formula like
=IF(ISERROR(FIND(" ",TRIM(D1),FIND("
",TRIM(D1),1)+1)),LEFT(TRIM(D1),FIND("
",TRIM(D1))),LEFT(TRIM(D1),FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)))
which should give you one or two forenames. In F, =trim(RIGHT(D1,4)) will
put the age there. Then Text to Columns on what's left with both space
and )
as delimiters gives you the rest. If in stage 3 of the Text to Columns
wizard you mark the left-hand column as "Date", your date-like data will
turn into a date. All that's left then is to delete column D, and then
concatenate as you will into columns further right.

Please try this on a copy of your data first, and remember that the long
formula will only deal with one or two forenames (not three) and the
little
formula in F assumes that none of your patients is over 99.

--
Noel


"Luke Slotwinski" wrote in
message ...
Nick:

Thank you for your help... I am running into a problem though.
If I set the Text to Columns for an 8 entry string format...
M1234 1112223 SHMOE, JOE ADAM 2Y(01/01/2005) M ER
then I have an entry w/o a middle name that is 7 entries long
T2323 5551212 EXAMPLE, GIRL 3Y(01/01/2004) F AUC
It will paste 3Y(01/01/2004) into E* when I'd like to have it in F*...
is
there a way to limit column E* to alpha only and if there is numerics
to
skip
to colum F and paste?

Thank you,
Luke Slotwinski
"Nick Hodge" wrote:

Luke

I would split these using DataText to columns.... using space as the
delimiter. This will split them into the 7/8 fields. Then you can
re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can
re-assemble
them
in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the helper
columns
and EditPaste Special...Values to themselves to kill the formulae
and
delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Luke Slotwinski" wrote in
message ...
Sorry got home from work and realized I made a mistake in this
description...
I need the paste string to be distributed accross a row not down a
column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple
cells
within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so
the
string
length varies from 7 and 8 entries (seperated by commas). But
there
will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it
would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

That did it... thanks so much Noel!!

Luke Slotwinski

"Ildhund" wrote:

See what I mean about frustrating? Try again:

*On a copy of your data*:

(i) With your strings in column A, select the data and then pick Text to
Columns delimited with space and comma as the delimiter. In step 3, mark all
but the first three columns "Do not import column (skip)" and change the
destination from $A$1 to $B$1. This should give you M1234 in B1, 1112223 in
C1 and SHMOE in D1.
(ii) Pick Text to Columns delimited again. This time, make your delimiter
Other, "(". In step 3, mark the first column "Do not import..." and change
the destination to $G$1. You should now have 01/01/2005) M ER in G1.
(iii) Select column G, pick Text to Columns fixed width. At step 2, click to
the left of the ")" to create an extra column containing just ")". At step
3, mark the first column "Date" and select your format from the dropdown,
Mark the ")" column "Do not import..." and Finish. You should now have
01/01/2005 in G1, M in H1 and ER in I1.
(iii) In E1, put =TRIM(MID(A1,FIND(",",A1)+2,FIND("(",A1)-FIND(",",A1)-5))
You should now have one or two forenames in E. Copy the formula down as far
as you need to go (eg. by double clicking on the little black square in the
bottom right-hand corner of E1 when it is the active cell).
(iv) In F1, put =VALUE(MID(A1,FIND("(",A1)-3,2)). A one- or two-digit age
should result. Copy the formula down.

This takes much longer to write out than it does to perform, and is
submitted with every conceivable caveat. If you end up with a table you
think you can use, the last stage before concatenating would be to select
the whole table, copy and then paste special values (to get rid of the
formulae). And a bit of advice from one who's often been there and done
that: forget about the concatenation - leave the data separated by columns.
It will be so much easier to search and sort and filter, which you (or
someone else) will probably want to do sometime. You can always consolidate
data from columns when you get round to producing a printed report.

Whew!

--
Noel



"Luke Slotwinski" wrote in
message ...
Ildhund:

Thank you so much for your help. Sorry to keep complicating matters but
the
first two entries arent always the same character count... It could be
M1-M19999 and the second entry 1-9 characxters long... kind of ruins the
fixed length delimiter.

Any thoughts?
Luke Slotwinski

"Ildhund" wrote:

One of those fascinating but oh so frustrating tasks unless you can trust
the original data to be consistent! I just played around for a few
minutes
and came up with:
With your string in column A, Text to Columns fixed width could give you
M1234 in A, 1112223 in B and the rest in C. Then Text to Columns again on
C,
this time with comma as your delimiter will put the surname in C and the
rest in D. Text to Columns again on D with ( as the delimiter leaves the
forename(s)and age in D and the rest in E. Now insert a couple of columns
between D and E. In E, put in a formula like
=IF(ISERROR(FIND(" ",TRIM(D1),FIND("
",TRIM(D1),1)+1)),LEFT(TRIM(D1),FIND("
",TRIM(D1))),LEFT(TRIM(D1),FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)))
which should give you one or two forenames. In F, =trim(RIGHT(D1,4)) will
put the age there. Then Text to Columns on what's left with both space
and )
as delimiters gives you the rest. If in stage 3 of the Text to Columns
wizard you mark the left-hand column as "Date", your date-like data will
turn into a date. All that's left then is to delete column D, and then
concatenate as you will into columns further right.

Please try this on a copy of your data first, and remember that the long
formula will only deal with one or two forenames (not three) and the
little
formula in F assumes that none of your patients is over 99.

--
Noel


"Luke Slotwinski" wrote in
message ...
Nick:

Thank you for your help... I am running into a problem though.
If I set the Text to Columns for an 8 entry string format...
M1234 1112223 SHMOE, JOE ADAM 2Y(01/01/2005) M ER
then I have an entry w/o a middle name that is 7 entries long
T2323 5551212 EXAMPLE, GIRL 3Y(01/01/2004) F AUC
It will paste 3Y(01/01/2004) into E* when I'd like to have it in F*...
is
there a way to limit column E* to alpha only and if there is numerics
to
skip
to colum F and paste?

Thank you,
Luke Slotwinski
"Nick Hodge" wrote:

Luke

I would split these using DataText to columns.... using space as the
delimiter. This will split them into the 7/8 fields. Then you can
re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can
re-assemble
them
in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the helper
columns
and EditPaste Special...Values to themselves to kill the formulae
and
delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Luke Slotwinski" wrote in
message ...
Sorry got home from work and realized I made a mistake in this
description...
I need the paste string to be distributed accross a row not down a
column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple
cells
within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name so
the
string
length varies from 7 and 8 entries (seperated by commas). But
there
will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it
would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski

You're welcome.

Of course, if this is something that's going to have to be done on a regular
basis, you'd probably be better off with some code that would also be able
to take care of all the inconsistent data that will probably turn up. (I
have visions of the reaction of some eager secretary to a conversation like
this:
"What's his middle name?"
"Well actually it's Ponsonby, but we never use it."
"Oh, all right, I'll just put it in brackets so we'll know not to use it.")

Glad it's working.

--
Noel

"Luke Slotwinski" wrote in
message ...
That did it... thanks so much Noel!!

Luke Slotwinski

"Ildhund" wrote:

See what I mean about frustrating? Try again:

*On a copy of your data*:

(i) With your strings in column A, select the data and then pick Text to
Columns delimited with space and comma as the delimiter. In step 3, mark
all
but the first three columns "Do not import column (skip)" and change the
destination from $A$1 to $B$1. This should give you M1234 in B1, 1112223
in
C1 and SHMOE in D1.
(ii) Pick Text to Columns delimited again. This time, make your delimiter
Other, "(". In step 3, mark the first column "Do not import..." and
change
the destination to $G$1. You should now have 01/01/2005) M ER in G1.
(iii) Select column G, pick Text to Columns fixed width. At step 2, click
to
the left of the ")" to create an extra column containing just ")". At
step
3, mark the first column "Date" and select your format from the dropdown,
Mark the ")" column "Do not import..." and Finish. You should now have
01/01/2005 in G1, M in H1 and ER in I1.
(iii) In E1, put
=TRIM(MID(A1,FIND(",",A1)+2,FIND("(",A1)-FIND(",",A1)-5))
You should now have one or two forenames in E. Copy the formula down as
far
as you need to go (eg. by double clicking on the little black square in
the
bottom right-hand corner of E1 when it is the active cell).
(iv) In F1, put =VALUE(MID(A1,FIND("(",A1)-3,2)). A one- or two-digit age
should result. Copy the formula down.

This takes much longer to write out than it does to perform, and is
submitted with every conceivable caveat. If you end up with a table you
think you can use, the last stage before concatenating would be to select
the whole table, copy and then paste special values (to get rid of the
formulae). And a bit of advice from one who's often been there and done
that: forget about the concatenation - leave the data separated by
columns.
It will be so much easier to search and sort and filter, which you (or
someone else) will probably want to do sometime. You can always
consolidate
data from columns when you get round to producing a printed report.

Whew!

--
Noel



"Luke Slotwinski" wrote in
message ...
Ildhund:

Thank you so much for your help. Sorry to keep complicating matters
but
the
first two entries arent always the same character count... It could be
M1-M19999 and the second entry 1-9 characxters long... kind of ruins
the
fixed length delimiter.

Any thoughts?
Luke Slotwinski

"Ildhund" wrote:

One of those fascinating but oh so frustrating tasks unless you can
trust
the original data to be consistent! I just played around for a few
minutes
and came up with:
With your string in column A, Text to Columns fixed width could give
you
M1234 in A, 1112223 in B and the rest in C. Then Text to Columns again
on
C,
this time with comma as your delimiter will put the surname in C and
the
rest in D. Text to Columns again on D with ( as the delimiter leaves
the
forename(s)and age in D and the rest in E. Now insert a couple of
columns
between D and E. In E, put in a formula like
=IF(ISERROR(FIND(" ",TRIM(D1),FIND("
",TRIM(D1),1)+1)),LEFT(TRIM(D1),FIND("
",TRIM(D1))),LEFT(TRIM(D1),FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)))
which should give you one or two forenames. In F, =trim(RIGHT(D1,4))
will
put the age there. Then Text to Columns on what's left with both space
and )
as delimiters gives you the rest. If in stage 3 of the Text to Columns
wizard you mark the left-hand column as "Date", your date-like data
will
turn into a date. All that's left then is to delete column D, and then
concatenate as you will into columns further right.

Please try this on a copy of your data first, and remember that the
long
formula will only deal with one or two forenames (not three) and the
little
formula in F assumes that none of your patients is over 99.

--
Noel


"Luke Slotwinski" wrote in
message ...
Nick:

Thank you for your help... I am running into a problem though.
If I set the Text to Columns for an 8 entry string format...
M1234 1112223 SHMOE, JOE ADAM 2Y(01/01/2005) M ER
then I have an entry w/o a middle name that is 7 entries long
T2323 5551212 EXAMPLE, GIRL 3Y(01/01/2004) F AUC
It will paste 3Y(01/01/2004) into E* when I'd like to have it in
F*...
is
there a way to limit column E* to alpha only and if there is
numerics
to
skip
to colum F and paste?

Thank you,
Luke Slotwinski
"Nick Hodge" wrote:

Luke

I would split these using DataText to columns.... using space as
the
delimiter. This will split them into the 7/8 fields. Then you can
re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can
re-assemble
them
in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the
helper
columns
and EditPaste Special...Values to themselves to kill the formulae
and
delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Luke Slotwinski" wrote
in
message ...
Sorry got home from work and realized I made a mistake in this
description...
I need the paste string to be distributed accross a row not down
a
column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple
cells
within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string
entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 -- should go to Cell A1
Doe, Jane Mary -- should go to Cell A2
33Y(05/05/2000) F - should go to Cell A3
ER -- to go to Cell A4
*what complicates matters is there is not always a middle name
so
the
string
length varies from 7 and 8 entries (seperated by commas). But
there
will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1,
it
would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski