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

Comments

It's nice but it doesn't allow us to make sequences with skipped letters such as "a, c, e, g, i, etc...". Isn't there a way to have this?

ChameleonScales gravatar imageChameleonScales ( 2019-07-02 14:03:59 +0200 )edit
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,472 times

Last updated: Mar 17 '16