Vertical Text

[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

[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



"Brian" wrote:

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

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

Brian wrote:
>
> 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

--

Dave Peterson

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

"Brian" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
>

[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


"Brian" wrote:

> 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?
>
> "Brian" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
> >
>
>
>

[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

Brian wrote:
>
> 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?
>
> "Brian" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
> >

--

Dave Peterson

[Brian]

Thanks Chuck, works like a charm.

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


"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Brian" wrote:
>
>> 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?
>>
>> "Brian" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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
>> >
>> >
>> >
>>
>>
>>

[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



"Brian" wrote:

> Thanks Chuck, works like a charm.
>
> BTW, why can't I just format the original numbers with the format painter
> (doesn't work)?
>
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "Brian" wrote:
> >
> >> 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?
> >>
> >> "Brian" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

[Brian]

Thanks again Chuck.

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

"CLR" <[email protected]> wrote in message
news:[email protected]...
> '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
>
>
>
> "Brian" wrote:
>
>> Thanks Chuck, works like a charm.
>>
>> BTW, why can't I just format the original numbers with the format painter
>> (doesn't work)?
>>
>>
>> "CLR" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>> > "Brian" wrote:
>> >
>> >> 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?
>> >>
>> >> "Brian" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[CLR]

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

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

"Brian" wrote:

> Thanks again Chuck.
>
> BTW, I hope that CABGx3 in you sig doesn't mean you have had a triple
> bypass!!!
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > '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
> >
> >
> >
> > "Brian" wrote:
> >
> >> Thanks Chuck, works like a charm.
> >>
> >> BTW, why can't I just format the original numbers with the format painter
> >> (doesn't work)?
> >>
> >>
> >> "CLR" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >> > "Brian" wrote:
> >> >
> >> >> 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?
> >> >>
> >> >> "Brian" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > 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
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[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!
==============================================


"Brian" <[email protected]> wrote in message
news:[email protected]...
> Thanks again Chuck.
>
> BTW, I hope that CABGx3 in you sig doesn't mean you have had a triple
> bypass!!!
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > '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
> >
> >
> >
> > "Brian" wrote:
> >
> >> Thanks Chuck, works like a charm.
> >>
> >> BTW, why can't I just format the original numbers with the format
painter
> >> (doesn't work)?
> >>
> >>
> >> "CLR" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Use this formula.........
> >> >
=LEFT(A1,1)&CHAR(10)&MID(A1,2,2)&CHAR(10)&CHAR(10)&MID(A1,5,5)-1&CHAR(10)&RI
GHT(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
> >> >
> >> >
> >> > "Brian" wrote:
> >> >
> >> >> 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?
> >> >>
> >> >> "Brian" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > 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
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

[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