Excel Help

[Homerboy]

I'm generating some Excel files off of a SSIS package querying a SQL database.

One column of cells sometimes will have multiple values such as:

433170800000 2011 / 462092000070 2011

With the values being separated with the "/". There could be 2, 3... 10 distinct values all separated with the "/"

I'm trying to think of a way via conditional formatting or an adjacent column (then hiding the original column) or something to format those so that the "/" is removed AND is replace with a carriage return (Alt+Enter) so that the cell then is still one row, but double thick.

433170800000 2011
462092000070 2011

Make sense?

 

[imagoon]

=substitute(a1,"/",char(10))
or
=substitute(a1," / ",char(10))
if leading and trailing spaces matter.

Make sure wrap text is on so Excel doesn't ignore the linefeeds.

 

[Homerboy]

=substitute(a1,"/",char(10))
or
=substitute(a1," / ",char(10))
if leading and trailing spaces matter.

Make sure wrap text is on so Excel doesn't ignore the linefeeds.

Funny I had that formula worked out... and it wasn't working.
Then I realized (googled) "Wrap text"..... DUH

Thanks!

 

[Homerboy]

ah crap... I just realized that I can't do it this way.
Number of rows will very from report to report (one day could be 10 rows, the next 500)

It's like I need to apply the formula to the whole column via formatting...

 

[imagoon]

Problem is that formulas are not formatting. What does the sheet contain? Is there anything like line numbers or --end of report-- or anything of that nature on it?

 

[Homerboy]

Problem is that formulas are not formatting. What does the sheet contain? Is there anything like line numbers or --end of report-- or anything of that nature on it?

Yeah I know...
I just said "screw it" and left word wrap on, then made the column with a fixed width so it "separates" the values.

It works well enough for the client's need/knowledge level 🙂

Thanks!