Formatting vertical text in a column

[edjohnson1]

I want to enter a short (< 10 words) text string in vertical format in a column. The problem is that I want the text to spill into adjacent cells, vertically, the way text would normally do horizontally. I do NOT want the cell heighth (i.e. row heighth) to be re-sized NOR do want to to merge cells. When I go to "Cells / Format / Format Cells / Alignment", I can easily change the text orientation to vertical but it then resizes the heighth of the cell (row) to accomodate rather than allowing the text string to spill into adjacent cells as it would normally do when the text is in horizontal orientation. Excel 2007.

 

[jasonb75]

Ruddles, =MID("This is a test",ROW(Z99)-ROW(Z$99)+1,1) would be the same as =MID("This is a test",ROW(A1),1) moving the formula to row 99, doesn't mean you can't use A1 as a reference.

Possibly everything suggested is unsuitable, based on row height being fixed and requirement for a continuous text string I've come up with another wild idea

First off create 2 named ranges

name. rowh
refers to. =GET.CELL(17,INDIRECT("RC",0))

name. fontp
refers to. =GET.CELL(19,INDIRECT("RC",0))

Resize rows, set font, size, etc as desired.

Array formula, fill down to cover range as required.

=MID("A few short words to see if this works or not.",1+SUM(LEN(A$2:A2)-LEN(A$2)),ROUNDUP((rowh/(fontp*1.5)),0))

A$2 / A2 refers to cell directly above the formula, worksheet needs to be recalculated if row height or font size are changed. Only tested with a couple of common fonts so might fail elsewhere.

 

[Ruddles]

Jasonb75, yes, those were alternative: one using a cell reference as the source string and the other using an embedded string.

Did you try either of my suggestions? I thought they had the merit of simplicity.

Ed, are you still with us?

 

[jasonb75]

Ruddles, I tried your suggestions, which work well and may be an ideal solution, I was trying to find a solution that allowed for varied row height rather than a default number of characters, the solution is a bit wild but at least it is vba free

Not sure if you missed what was actually poinitng out with your solutions, the simplicity is great, but to make them even more simple

your way to return start position to MID, ROW(Z99)-ROW(Z$99)+1

could be simplified by using, ROW(A1)

I wasn't trying to come across as picking fault, merely trying to point out something you may not have realised possible.

 

[Ruddles]

ROW(Z99)-ROW(Z$99)+1
could be simplified by using, ROW(A1)

Yes, sorry, I wasn't being deliberately grumpy. I've just looked at it again and you're absolutely correct - I was over-engineering the solution! I thought I'd have to (a) nail the start reference and (b) allow the end reference to float, whereas all I actually needed to do was allow the whole reference to float.

D'oh!