How to take a cell that has 3 values and make 2 more new lines

Phil · 10-21-2005, 01:05 PM

Hello,

I have a cell that has 3 values in it, and want to BREAK it up so that
instead, I will get 2 MORE NEW rows, for a total now of 3 rows. I believe
the values were inserted into the cell using the CTRL+ENTER key combination.
There is a non-printable character (ANSI 0010) visible in the EDIT window to
make the new line.

Also, there are instances where it may be only 2 values (which occurs MOST
of the time), or other instances where there may be as much as 5 values in
the same cell.

Here is a typical cell: (obviously you can disregard the COL and ROW
references)

COL- A COL - B COL - C
ROW-1 Tract_No Parcel_ID Owner
ROW-2 8-5-012-045 251254 Floyd Daniels
ROW-3 7-5-065-012
ROW-4 8-6-045-012

So what I want to do is to create a 2 MORE new rows to read like this:

COL- A COL - B COL - C
ROW-1 Tract_No Parcel_ID Owner
ROW-2 8-5-012-045 251254 Floyd Daniels
ROW-3 7-5-065-012 251254 Floyd Daniels
ROW-4 8-6-045-012 251254 Floyd Daniels

How can this be done?

TIA for your replies.

Phil.

Anne Troy · 10-21-2005, 02:05 PM

Try this, Phil:

http://www.officearticles.com/excel/...soft_excel.htm
************
Anne Troy
www.OfficeArticles.com

"Phil" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a cell that has 3 values in it, and want to BREAK it up so that
> instead, I will get 2 MORE NEW rows, for a total now of 3 rows. I believe
> the values were inserted into the cell using the CTRL+ENTER key
> combination.
> There is a non-printable character (ANSI 0010) visible in the EDIT window
> to
> make the new line.
>
> Also, there are instances where it may be only 2 values (which occurs MOST
> of the time), or other instances where there may be as much as 5 values in
> the same cell.
>
> Here is a typical cell: (obviously you can disregard the COL and ROW
> references)
>
> COL- A COL - B COL - C
> ROW-1 Tract_No Parcel_ID Owner
> ROW-2 8-5-012-045 251254 Floyd Daniels
> ROW-3 7-5-065-012
> ROW-4 8-6-045-012
>
> So what I want to do is to create a 2 MORE new rows to read like this:
>
> COL- A COL - B COL - C
> ROW-1 Tract_No Parcel_ID Owner
> ROW-2 8-5-012-045 251254 Floyd Daniels
> ROW-3 7-5-065-012 251254 Floyd Daniels
> ROW-4 8-6-045-012 251254 Floyd Daniels
>
> How can this be done?
>
> TIA for your replies.
>
> Phil.

Phil · 10-23-2005, 06:05 PM

Anne,

You know, as I re-read this now, I realize that I did not correctly state my
case.

This is how it looks below, graphically speaking (Row 1 contains the col.
headings):

Col- A Col - B Col - C Col-D
Row-1 Tract_No Parcel_ID Owner
Row-2 8-5-012-045 251254 Floyd Daniels
7-5-065-012
8-6-045-012
Row-3 7-9-001-105 036584 James Clark
Row-4 8-5-900-012 214566 Mary Jones

As you can see what is occuring, is that the 2nd row ACTUALLY contains 3
values in the same cell. So, if you navigated to cell B2, and to hit F2 and
look in the edit field above, it would look like this:

8-5-012-045
7-5-065-012
8-6-045-012

Where ALL 3 values are stacked up in the SAME cell window.

So what I WANT to do is to take the value that resides in B2, and create 2
NEW rows below it, then copy the other values from C2 and D2, and paste them
on the new 2nd and 3rd rows.

So my NEW output will look like this:

Col- A Col - B Col - C Col-D
Row-1 Tract_No Parcel_ID Owner
Row-2 8-5-012-045 251254 Floyd Daniels
Row-3 7-5-065-012 251254 Floyd Daniels
Row-4 8-6-045-012 251254 Floyd Daniels
Row-5 7-9-001-105 036584 James Clark
Row-6 8-5-900-012 214566 Mary Jones

FWIW: Incidentally, if you copied the cell B2 to a text editor, you will
also see the 3 values like this, with the quotes at the end.

"8-5-012-045
7-5-065-012
8-6-045-012"

But if you paste it in Access it shows something like this:

"8-5-012-045*7-5-065-012*8-6-045-012"

Note: I am using the asterisk character to show the location of the ANSI
(CHAR 10) character, which looks like a small square.

Does that explain it better? I apologize for mis-representing my OP in the
1st place. I hope that you all can help me on this, as there are over a 100
of these on just this one sheet, and I have 3 or more of these to do.

TIA again for your replies!

Phil.

"Anne Troy" wrote:

> Try this, Phil:
>
> http://www.officearticles.com/excel/...soft_excel.htm
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "Phil" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have a cell that has 3 values in it, and want to BREAK it up so that
> > instead, I will get 2 MORE NEW rows, for a total now of 3 rows. I believe
> > the values were inserted into the cell using the CTRL+ENTER key
> > combination.
> > There is a non-printable character (ANSI 0010) visible in the EDIT window
> > to
> > make the new line.
> >
> > Also, there are instances where it may be only 2 values (which occurs MOST
> > of the time), or other instances where there may be as much as 5 values in
> > the same cell.
> >
> > Here is a typical cell: (obviously you can disregard the COL and ROW
> > references)
> >
> > COL- A COL - B COL - C
> > ROW-1 Tract_No Parcel_ID Owner
> > ROW-2 8-5-012-045 251254 Floyd Daniels
> > ROW-3 7-5-065-012
> > ROW-4 8-6-045-012
> >
> > So what I want to do is to create a 2 MORE new rows to read like this:
> >
> > COL- A COL - B COL - C
> > ROW-1 Tract_No Parcel_ID Owner
> > ROW-2 8-5-012-045 251254 Floyd Daniels
> > ROW-3 7-5-065-012 251254 Floyd Daniels
> > ROW-4 8-6-045-012 251254 Floyd Daniels
> >
> > How can this be done?
> >
> > TIA for your replies.
> >
> > Phil.
>
>
>

Anne Troy · 10-23-2005, 06:05 PM

Ouch, Phil. I'd be getting my butt over to the Excel beginner VBA group.
Possibly even upload a copy of your file or a piece of it and provide a
link. (Tho many won't look at attachments, there are those who will.)
Likely, there's some coder out there who can at least get you into the
position I THOUGHT you were in, and you could go from there.

************
Anne Troy
www.OfficeArticles.com

"Phil" <[email protected]> wrote in message
news:[email protected]...
> Anne,
>
> You know, as I re-read this now, I realize that I did not correctly state
> my
> case.
>
> This is how it looks below, graphically speaking (Row 1 contains the col.
> headings):
>
> Col- A Col - B Col - C Col-D
> Row-1 Tract_No Parcel_ID Owner
> Row-2 8-5-012-045 251254 Floyd Daniels
> 7-5-065-012
> 8-6-045-012
> Row-3 7-9-001-105 036584 James Clark
> Row-4 8-5-900-012 214566 Mary Jones
>
> As you can see what is occuring, is that the 2nd row ACTUALLY contains 3
> values in the same cell. So, if you navigated to cell B2, and to hit F2
> and
> look in the edit field above, it would look like this:
>
> 8-5-012-045
> 7-5-065-012
> 8-6-045-012
>
> Where ALL 3 values are stacked up in the SAME cell window.
>
> So what I WANT to do is to take the value that resides in B2, and create 2
> NEW rows below it, then copy the other values from C2 and D2, and paste
> them
> on the new 2nd and 3rd rows.
>
> So my NEW output will look like this:
>
> Col- A Col - B Col - C Col-D
> Row-1 Tract_No Parcel_ID Owner
> Row-2 8-5-012-045 251254 Floyd Daniels
> Row-3 7-5-065-012 251254 Floyd Daniels
> Row-4 8-6-045-012 251254 Floyd Daniels
> Row-5 7-9-001-105 036584 James Clark
> Row-6 8-5-900-012 214566 Mary Jones
>
> FWIW: Incidentally, if you copied the cell B2 to a text editor, you will
> also see the 3 values like this, with the quotes at the end.
>
> "8-5-012-045
> 7-5-065-012
> 8-6-045-012"
>
> But if you paste it in Access it shows something like this:
>
> "8-5-012-045*7-5-065-012*8-6-045-012"
>
> Note: I am using the asterisk character to show the location of the ANSI
> (CHAR 10) character, which looks like a small square.
>
> Does that explain it better? I apologize for mis-representing my OP in the
> 1st place. I hope that you all can help me on this, as there are over a
> 100
> of these on just this one sheet, and I have 3 or more of these to do.
>
> TIA again for your replies!
>
> Phil.
>
> "Anne Troy" wrote:
>
>> Try this, Phil:
>>
>> http://www.officearticles.com/excel/...soft_excel.htm
>> ************
>> Anne Troy
>> www.OfficeArticles.com
>>
>> "Phil" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have a cell that has 3 values in it, and want to BREAK it up so that
>> > instead, I will get 2 MORE NEW rows, for a total now of 3 rows. I
>> > believe
>> > the values were inserted into the cell using the CTRL+ENTER key
>> > combination.
>> > There is a non-printable character (ANSI 0010) visible in the EDIT
>> > window
>> > to
>> > make the new line.
>> >
>> > Also, there are instances where it may be only 2 values (which occurs
>> > MOST
>> > of the time), or other instances where there may be as much as 5 values
>> > in
>> > the same cell.
>> >
>> > Here is a typical cell: (obviously you can disregard the COL and ROW
>> > references)
>> >
>> > COL- A COL - B COL - C
>> > ROW-1 Tract_No Parcel_ID Owner
>> > ROW-2 8-5-012-045 251254 Floyd Daniels
>> > ROW-3 7-5-065-012
>> > ROW-4 8-6-045-012
>> >
>> > So what I want to do is to create a 2 MORE new rows to read like this:
>> >
>> > COL- A COL - B COL - C
>> > ROW-1 Tract_No Parcel_ID Owner
>> > ROW-2 8-5-012-045 251254 Floyd Daniels
>> > ROW-3 7-5-065-012 251254 Floyd Daniels
>> > ROW-4 8-6-045-012 251254 Floyd Daniels
>> >
>> > How can this be done?
>> >
>> > TIA for your replies.
>> >
>> > Phil.
>>
>>
>>

Phil · 10-23-2005, 06:05 PM

OK. Anne, I will try doing that.

Thanks.

"Anne Troy" wrote:

> Ouch, Phil. I'd be getting my butt over to the Excel beginner VBA group.
> Possibly even upload a copy of your file or a piece of it and provide a
> link. (Tho many won't look at attachments, there are those who will.)
> Likely, there's some coder out there who can at least get you into the
> position I THOUGHT you were in, and you could go from there.

> ************
> Anne Troy
> www.OfficeArticles.com
>
> "Phil" <[email protected]> wrote in message
> news:[email protected]...
> > Anne,
> >
> > You know, as I re-read this now, I realize that I did not correctly state
> > my
> > case.
> >
> > This is how it looks below, graphically speaking (Row 1 contains the col.
> > headings):
> >
> > Col- A Col - B Col - C Col-D
> > Row-1 Tract_No Parcel_ID Owner
> > Row-2 8-5-012-045 251254 Floyd Daniels
> > 7-5-065-012
> > 8-6-045-012
> > Row-3 7-9-001-105 036584 James Clark
> > Row-4 8-5-900-012 214566 Mary Jones
> >
> > As you can see what is occuring, is that the 2nd row ACTUALLY contains 3
> > values in the same cell. So, if you navigated to cell B2, and to hit F2
> > and
> > look in the edit field above, it would look like this:
> >
> > 8-5-012-045
> > 7-5-065-012
> > 8-6-045-012
> >
> > Where ALL 3 values are stacked up in the SAME cell window.
> >
> > So what I WANT to do is to take the value that resides in B2, and create 2
> > NEW rows below it, then copy the other values from C2 and D2, and paste
> > them
> > on the new 2nd and 3rd rows.
> >
> > So my NEW output will look like this:
> >
> > Col- A Col - B Col - C Col-D
> > Row-1 Tract_No Parcel_ID Owner
> > Row-2 8-5-012-045 251254 Floyd Daniels
> > Row-3 7-5-065-012 251254 Floyd Daniels
> > Row-4 8-6-045-012 251254 Floyd Daniels
> > Row-5 7-9-001-105 036584 James Clark
> > Row-6 8-5-900-012 214566 Mary Jones
> >
> > FWIW: Incidentally, if you copied the cell B2 to a text editor, you will
> > also see the 3 values like this, with the quotes at the end.
> >
> > "8-5-012-045
> > 7-5-065-012
> > 8-6-045-012"
> >
> > But if you paste it in Access it shows something like this:
> >
> > "8-5-012-045*7-5-065-012*8-6-045-012"
> >
> > Note: I am using the asterisk character to show the location of the ANSI
> > (CHAR 10) character, which looks like a small square.
> >
> > Does that explain it better? I apologize for mis-representing my OP in the
> > 1st place. I hope that you all can help me on this, as there are over a
> > 100
> > of these on just this one sheet, and I have 3 or more of these to do.
> >
> > TIA again for your replies!
> >
> > Phil.
> >
> > "Anne Troy" wrote:
> >
> >> Try this, Phil:
> >>
> >> http://www.officearticles.com/excel/...soft_excel.htm
> >> ************
> >> Anne Troy
> >> www.OfficeArticles.com
> >>
> >> "Phil" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > I have a cell that has 3 values in it, and want to BREAK it up so that
> >> > instead, I will get 2 MORE NEW rows, for a total now of 3 rows. I
> >> > believe
> >> > the values were inserted into the cell using the CTRL+ENTER key
> >> > combination.
> >> > There is a non-printable character (ANSI 0010) visible in the EDIT
> >> > window
> >> > to
> >> > make the new line.
> >> >
> >> > Also, there are instances where it may be only 2 values (which occurs
> >> > MOST
> >> > of the time), or other instances where there may be as much as 5 values
> >> > in
> >> > the same cell.
> >> >
> >> > Here is a typical cell: (obviously you can disregard the COL and ROW
> >> > references)
> >> >
> >> > COL- A COL - B COL - C
> >> > ROW-1 Tract_No Parcel_ID Owner
> >> > ROW-2 8-5-012-045 251254 Floyd Daniels
> >> > ROW-3 7-5-065-012
> >> > ROW-4 8-6-045-012
> >> >
> >> > So what I want to do is to create a 2 MORE new rows to read like this:
> >> >
> >> > COL- A COL - B COL - C
> >> > ROW-1 Tract_No Parcel_ID Owner
> >> > ROW-2 8-5-012-045 251254 Floyd Daniels
> >> > ROW-3 7-5-065-012 251254 Floyd Daniels
> >> > ROW-4 8-6-045-012 251254 Floyd Daniels
> >> >
> >> > How can this be done?
> >> >
> >> > TIA for your replies.
> >> >
> >> > Phil.
> >>
> >>
> >>
>
>
>