Ask Your Question
1

Can calc auto-increment letters?

asked 2016-03-17 12:28:34 +0200

David gravatar image

In Calc, if you fill in adjacent cells with incremented numbers, then select both cells and drag, Calc will auto-populate with the series, as in columns C and D here:

screencapture

I have tried doing this with letters -- "a", "b", > select, drag ... -- but it just repeats "a b a b a b" instead of incrementing the series.

So, can Calc do with letters what it does with numbers?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2016-03-17 20:33:08 +0200

pierre-yves samyn gravatar image

Hi

Why not simply:

=CHAR(ROW(A97))

Regards

edit flag offensive delete link more

Comments

Hmm.... It doesn't work for dragging across rows, only down columns. (Same for the solution from @karolus too, it seems.) But elegant for columns!

David gravatar imageDavid ( 2016-03-17 20:40:55 +0200 )edit

Of course: it was the question illustrated by the screenshot. For column:

=CHAR(COLUMN(CS1))

But no confusio:n in my mind this is not a better solution than the sort list :)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-03-17 20:48:27 +0200 )edit

Ah! I tried =CHAR(COLUMN(A97)) which of course did not work. :) Thanks for the added solution!

David gravatar imageDavid ( 2016-03-17 21:29:37 +0200 )edit

@David
For dragging across rows you need COLUMN() instead ROW()

karolus gravatar imagekarolus ( 2016-03-17 21:55:34 +0200 )edit

I like this simple and elegant answer!

Prashantk gravatar imagePrashantk ( 2018-10-19 00:10:11 +0200 )edit
2

answered 2016-03-17 13:18:50 +0200

David gravatar image

updated 2016-03-17 20:41:47 +0200

Not out of the box, but it can easily be set up.

To get this facility, go to Tools > Options... > LibreOffice Calc > Sort Lists. It should look something like this:

sort-list-option

Click on the New button on the upper right, and list the letters of the alphabet in the "Entries" box, one per line:

a
b
c
d
etc....

When you're finished, Click Add, then, OK. Now when you fill adjacent cells -- whether in rows or columns -- with "a", "b", then select them and drag, the series will be auto-incremented and -populated. Obviously this process can be used to set up whatever sequence you like, which is nice to know!

edit flag offensive delete link more
1

answered 2016-03-17 17:42:57 +0200

karolus gravatar image

Hallo

Incrementing from a up to amj by Formula:

=LOWER(SUBSTITUTE(ADDRESS(1;ROW();4);"1";""))
edit flag offensive delete link more

Comments

That is extremely cool. :) Thanks! Not a solution this non-expert calc-user could have found, though!

David gravatar imageDavid ( 2016-03-17 18:01:33 +0200 )edit

Could you supply some commentary? I can work out some, but not all. "LOWER" makes it lowercase; "SUBSTITUTE" takes the "ADDRESS" and transforms it ... but I'm not quite sure how the "1";"" fit in!

David gravatar imageDavid ( 2016-03-17 19:41:58 +0200 )edit

Hallo

SUBSTITUTEs "1" by "" ( empty strings )

karolus gravatar imagekarolus ( 2016-03-17 21:48:51 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-17 12:28:34 +0200

Seen: 2,068 times

Last updated: Mar 17 '16