Tuesday, February 1, 2011

yogi_Writing Vertically In Google Spreadsheet

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





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

9 comments:

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

    ReplyDelete
  2. Hi Julie:

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

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

    ReplyDelete
  4. The 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.

    I hope this helps.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  5. That's really helpful. Thankns for posting!

    ReplyDelete
  6. Thanks Yogi, my big problem is solved by your post :)

    ReplyDelete
  7. You Are Very Welcome Norman ... Now Let us Keep Googling.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  8. 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.

    However, 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...?

    ReplyDelete
  9. Hi Robert:

    You 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

    ReplyDelete