Conditional Formatting of Merged Cells

[Fishman4]

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.

 

[Gord Dibben]

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

 

[Fishman4]

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.

 

[David Biddulph]

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

 

[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?

 

[David Biddulph]

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(...

 

[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?