Vertical Text

B

Brian

Howdy All,

Just wanted to see if this is possible.

I want to display text in cells vertically so that cell A1which contains
0001 will appear like this:
0
0

0
1

With a space between the second and third zero.

Thanks,
Brian
 
C

CLR

Press Alt-Enter after entering each character and once more for the extra
"linefeed" between the zeros.........finish with a regular Enter"

Vaya con Dios,
Chuck, CABGx3
 
D

Dave Peterson

You can enter 1 in the cell
Format|cells|number tab
custom
00 00
Then go to the alignment tab to -90.

This will actually show the value on its side.

If that doesn't work for you, you could enter the value as:

0(alt-enter)
0(alt-enter)
(alt-enter)
0(alt-enter)
1

Alt-enter will force a new line within the cell.
 
B

Brian

Thanks for the replies.

But I need to format a whole worksheet like this, so I don't really want to
have to retype all the data.

Any way to format the text to display vertically?
 
C

CLR

Use this formula.........
=LEFT(A1,1)&CHAR(10)&MID(A1,2,2)&CHAR(10)&CHAR(10)&MID(A1,5,5)-1&CHAR(10)&RIGHT(A1,1)

Then, enter ONE cell somewhere off area formatted as advised previously by
hand, using the Alt-Enter after each character................then using the
FormatPainter, copy this format over to the above formulaed cells..........

Vaya con Dios,
Chuck, CABGx3
 
D

Dave Peterson

You could use a macro to change the values, too.

Select your range to fix and run this:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim myStr As String
Dim iCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants here!"
Exit Sub
End If

For Each myCell In myRng.Cells
myStr = myCell.Text
If Len(myStr) > 4 Then
MsgBox myCell.Address(0, 0) & " not changed!"
Else
myStr = Right("0000" & myStr, 4)
myStr = Mid(myStr, 1, 1) & vbLf _
& Mid(myStr, 2, 1) & vbLf & vbLf _
& Mid(myStr, 3, 1) & vbLf _
& Mid(myStr, 4, 1)
myCell.Value = myStr
myCell.WrapText = True
End If
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
B

Brian

Thanks Chuck, works like a charm.

BTW, why can't I just format the original numbers with the format painter
(doesn't work)?
 
C

CLR

'cause the CHAR() characters aren't in there in the original numbers...they
are kinda-sorta a part of the TEXT rather than part of the format
<G>............anyway, glad you got it working and thanks for the
feedback.............

Vaya con Dios,
Chuck, CABGx3
 
B

Brian

Thanks again Chuck.

BTW, I hope that CABGx3 in you sig doesn't mean you have had a triple
bypass!!!
 
C

CLR

Yup, 'bout 6 years ago.........doing fine now, thanks

Vaya con Dios,
Chuck=SUM(091938,USMCe4,DADx3,CABGx3,MMOUS2k)
 
R

RagDyer

You can do *exactly* what you want with formatting,
And then use the format painter to duplicate it in other cells.

Select A1, and start off with a custom format exactly as Dave suggested:
00 00

*Don't* hit OK yet,
Click on the "Alignment" tab,
And click in that tall, narrow box that displays 'Text' vertically,
NOW hit <OK>
And you've got exactly what you're looking for !
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
S

SteveG

Another option.

Excel has a built in Vertical Text tool that you can add to any
toolbar.

Right click on any toolbar and select Customize from the drop down.
The Customize option window appears. Select Format in the left hand
list and scroll down the list on the right until you find the Vertical
Text option. Select and drag this to any toolbar. Close the customize
option window.

Select the cell you wish to have the text displayed vertically and
click on the tool. The text now appears vertically. You can
incorporate a space wherever you want it by typing in the text the same
way i.e. 0001 should be typed in with the space 00 01. You can then use
the format painter to apply to all cells needed. That way as you type
in the data, it automatically converts to the vertical format.

HTH

Steve
 
Top