*Yogi Anand, D.Eng, P.E.*Google Spreadsheet

*www.energyefficientbuild.com*

**UPDATED Feb-07-2011 (added solution using REGEXREPLACE)**

Writing in vertical orientation via a command or menu is not supported in Google Spreadsheet; one can however accomplish this using a formula.

So let us say we want to write vertically in cell A1 the text housed in cell E1

then formula in cell A1 ...

=ArrayFormula(concatenate(mid(E1,row(indirect("z1:z"&len(E1))),1)&char(10)))

or using REGEXREPLACE

=regexreplace(A1,".","$1"&char(10))

or using REGEXREPLACE

=regexreplace(A1,".","$1"&char(10))

Now let us write vertically in cell A1 the text housed in cells C1 and D1

then formula in cell E1 is ... = C1&" "&D1

and formula in cell A1 ...

=ArrayFormula(concatenate(mid(E1,row(indirect("z1:z"&len(E1))),1)&char(10)))

or using REGEXREPLACE

=regexreplace(C1&" "&D1,"(.)","$1"&char(10))

or using REGEXREPLACE

=regexreplace(C1&" "&D1,"(.)","$1"&char(10))

Where do you input the formula? I keep trying and it doesn´t seem to work.

ReplyDeleteHi Julie:

ReplyDeleteThe formula is written in the cell in which you want the vertical writing to appear ... so in the two examples in this post, the formula is written in cell A1 and the result in writing in the vertical orientation.

I hope this helps.

I have not been able to deconstruct this formula. Would you mind explaining it?

ReplyDeleteThe formula in cell A1 looks at the TEXT in cell E1 and breaks it down into each individual character and then use of Char(10) rearranges each character in its own line thus the original TEXT in cell E1 is rearranged in vertical orientation in cell A1.

ReplyDeleteI hope this helps.

Cheers!

Yogi

Cloud Computing -- Google Docs Way

yogi--anand-consulting.blogspot.com

That's really helpful. Thankns for posting!

ReplyDeleteThanks Yogi, my big problem is solved by your post :)

ReplyDeleteYou Are Very Welcome Norman ... Now Let us Keep Googling.

ReplyDeleteCheers!

Yogi

Cloud Computing -- Google Docs Way

yogi--anand-consulting.blogspot.com

Hi Yogi, ok, so i tried this vertical writing thing...i am using it for a timesheet and want to keep my guys hours on it...so obviously writing horizontally takes up a lot of room.

ReplyDeleteHowever, i used your formula, then a few seconds later after everything looked great google docs went to save it and then it disappeared, showing up as "ERROR: Circular dependency detected"...but maybe this was only after I tried to copy and paste the formula so i could use it across an entire row...?

Hi Robert:

ReplyDeleteYou should be able to use formula across ... if it didn't work for you, chances are there is a mistake in the formula -- why don't you check your formula and let me know how it goes.

Make It A Great One

Cheers!

Yogi

Cloud Computing -- Google Docs Way

yogi--anand-consulting.blogspot.com