I have read this entire (well almost) forum and have found many near hits,
but nothing is working quite right. I get a report from an outside source
that I must format and distribute. The report is generated using SQL 2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run) over
a fixed number of columns. I need it to look like this:


L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out, but
only after the data is displayed in separate rows. I hope that this makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell without
splitting it, that would be grand!

--
Fishman4

OK. The cells are not merged. The data was entered by using ALT+ENTER.
--
Fishman4


"Fishman4" wrote:

I have read this entire (well almost) forum and have found many near hits,
but nothing is working quite right. I get a report from an outside source
that I must format and distribute. The report is generated using SQL 2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run) over
a fixed number of columns. I need it to look like this:


L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out, but
only after the data is displayed in separate rows. I hope that this makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell without
splitting it, that would be grand!

--
Fishman4

I will give an example for A1 with 3 rows of text separated by CR's(alt + enter)

Insert 2 new blank cells(or rows) below row 1

Select the cell and select the text in the formula bar.

Cut the contents.

Select any other cell to get out of edit mode.

Select A1:A3 and Paste.


Gord Dibben MS Excel MVP


On Tue, 13 Feb 2007 15:17:02 -0800, Fishman4
wrote:

OK. The cells are not merged. The data was entered by using ALT+ENTER.

OK. I am making progress. I have been able to separate the data in the
cells into individual columns by using this formula:
=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

Since there are three lines of data, I have to run this in the second column
and I get two of the lines of data moved. Then I run it in the third column
to get it down to just one. Now the problem is the conditional formatting.
When I apply CF on the third column, i get no results. If I remove the
formula and simply enter text the formatting works. What gives?

Don F.

--
Fishman4


"Fishman4" wrote:

I have read this entire (well almost) forum and have found many near hits,
but nothing is working quite right. I get a report from an outside source
that I must format and distribute. The report is generated using SQL 2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run) over
a fixed number of columns. I need it to look like this:


L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out, but
only after the data is displayed in separate rows. I hope that this makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell without
splitting it, that would be grand!

--
Fishman4

Is your CF looking for text or for numbers?
Your RIGHT() function will return text.
--
David Biddulph

"Fishman4" wrote in message
...
OK. I am making progress. I have been able to separate the data in the
cells into individual columns by using this formula:
=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

Since there are three lines of data, I have to run this in the second
column
and I get two of the lines of data moved. Then I run it in the third
column
to get it down to just one. Now the problem is the conditional
formatting.
When I apply CF on the third column, i get no results. If I remove the
formula and simply enter text the formatting works. What gives?

Don F.

--
Fishman4


"Fishman4" wrote:

I have read this entire (well almost) forum and have found many near
hits,
but nothing is working quite right. I get a report from an outside
source
that I must format and distribute. The report is generated using SQL
2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run)
over
a fixed number of columns. I need it to look like this:


L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out,
but
only after the data is displayed in separate rows. I hope that this
makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell
without
splitting it, that would be grand!

--
Fishman4

Not sure. In the CF dropdown, I select 'cell value is' and then begin to set
my parameters: less than, greater than, etc. Do I need to convert the result
of the formula from text to a number?
--
Fishman4


"David Biddulph" wrote:

Is your CF looking for text or for numbers?
Your RIGHT() function will return text.
--
David Biddulph

"Fishman4" wrote in message
...
OK. I am making progress. I have been able to separate the data in the
cells into individual columns by using this formula:
=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

Since there are three lines of data, I have to run this in the second
column
and I get two of the lines of data moved. Then I run it in the third
column
to get it down to just one. Now the problem is the conditional
formatting.
When I apply CF on the third column, i get no results. If I remove the
formula and simply enter text the formatting works. What gives?

Don F.

--
Fishman4


"Fishman4" wrote:

I have read this entire (well almost) forum and have found many near
hits,
but nothing is working quite right. I get a report from an outside
source
that I must format and distribute. The report is generated using SQL
2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run)
over
a fixed number of columns. I need it to look like this:


L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out,
but
only after the data is displayed in separate rows. I hope that this
makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell
without
splitting it, that would be grand!

--
Fishman4

Yes, if you are looking for numbers, such as 3, rather than text "3", then
you'll need to convert the text to a number.
It may be worth trying the double unary minus trick
=--RIGHT(...
--
David Biddulph

"Fishman4" wrote in message
...
Not sure. In the CF dropdown, I select 'cell value is' and then begin to
set
my parameters: less than, greater than, etc. Do I need to convert the
result
of the formula from text to a number?
--
Fishman4


"David Biddulph" wrote:

Is your CF looking for text or for numbers?
Your RIGHT() function will return text.
--
David Biddulph

"Fishman4" wrote in message
...
OK. I am making progress. I have been able to separate the data in
the
cells into individual columns by using this formula:
=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

Since there are three lines of data, I have to run this in the second
column
and I get two of the lines of data moved. Then I run it in the third
column
to get it down to just one. Now the problem is the conditional
formatting.
When I apply CF on the third column, i get no results. If I remove the
formula and simply enter text the formatting works. What gives?

Don F.

--
Fishman4


"Fishman4" wrote:

I have read this entire (well almost) forum and have found many near
hits,
but nothing is working quite right. I get a report from an outside
source
that I must format and distribute. The report is generated using SQL
2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways
from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run)
over
a fixed number of columns. I need it to look like this:


L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out,
but
only after the data is displayed in separate rows. I hope that this
makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell
without
splitting it, that would be grand!

--
Fishman4

I don't know what that means, but if you mean to put two minus signs between
the '=' and the 'right', that didn't work. Any way to convert to numbers?
--
Fishman4


"David Biddulph" wrote:

Yes, if you are looking for numbers, such as 3, rather than text "3", then
you'll need to convert the text to a number.
It may be worth trying the double unary minus trick
=--RIGHT(...
--
David Biddulph

"Fishman4" wrote in message
...
Not sure. In the CF dropdown, I select 'cell value is' and then begin to
set
my parameters: less than, greater than, etc. Do I need to convert the
result
of the formula from text to a number?
--
Fishman4


"David Biddulph" wrote:

Is your CF looking for text or for numbers?
Your RIGHT() function will return text.
--
David Biddulph

"Fishman4" wrote in message
...
OK. I am making progress. I have been able to separate the data in
the
cells into individual columns by using this formula:
=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

Since there are three lines of data, I have to run this in the second
column
and I get two of the lines of data moved. Then I run it in the third
column
to get it down to just one. Now the problem is the conditional
formatting.
When I apply CF on the third column, i get no results. If I remove the
formula and simply enter text the formatting works. What gives?

Don F.

--
Fishman4


"Fishman4" wrote:

I have read this entire (well almost) forum and have found many near
hits,
but nothing is working quite right. I get a report from an outside
source
that I must format and distribute. The report is generated using SQL
2003
Reporting Services and it comes in EXCEL but it is formatted 40 ways
from
Sunday i.e.;merged cells borders, etc. Here is my dilema: I have rows
containing data in this format

L M N O
3/30 4/16 6/22 7/3
10 3/22 4/12 6/18 7/1
8 4 4 2

This is repeated for many rows (it varies each time the report is run)
over
a fixed number of columns. I need it to look like this:


L M N O
11 3/30 4/16 6/22 7/3
12 3/22 4/12 6/18 7/1
13 8 4 4 2

Another twist. I need to run conditional formatting that will color a
particular cell based on its value. I have the formatting worked out,
but
only after the data is displayed in separate rows. I hope that this
makes
sense. Any help you can provide would be greatly appreciated

Don F.

P.S. If it is possible to apply the formatting to the merged cell
without
splitting it, that would be grand!

--
Fishman4