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

Posted by at
Labels:

9 comments:

  1. UnknownApril 13, 2011 at 9:38 AM

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

    ReplyDelete
  2. Yogi AnandApril 13, 2011 at 2:11 PM

    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. UnknownSeptember 3, 2012 at 7:12 PM

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

    ReplyDelete
  4. Yogi AnandSeptember 3, 2012 at 9:12 PM

    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. Richard WadeSeptember 6, 2012 at 12:47 PM

    That's really helpful. Thankns for posting!

    ReplyDelete
  6. AnonymousSeptember 17, 2012 at 11:22 PM

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

    ReplyDelete
  7. Yogi AnandSeptember 18, 2012 at 7:00 AM

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

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

    ReplyDelete
  8. UnknownApril 10, 2013 at 8:33 AM

    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. Yogi AnandApril 10, 2013 at 12:52 PM

    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

Subscribe to: Post Comments (Atom)