Ask Your Question
1

Can calc auto-increment letters? [closed]

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

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-01 20:07:04.868490

3 Answers

Sort by » oldest newest most voted
1

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

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 +0100 )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 +0100 )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 +0100 )edit

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

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

I like this simple and elegant answer!

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

pierre-yves samyn: Why not simply: =CHAR(ROW(A97))

because that's binding the letters to the row numbers ... and thus not universal.

newbie-02 gravatar imagenewbie-02 ( 2019-11-23 14:17:56 +0100 )edit
3

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

David gravatar image

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

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 +0100 )edit

this is the better solution! - pls. accept it and move it to the top.

newbie-02 gravatar imagenewbie-02 ( 2019-11-23 14:23:47 +0100 )edit

This solution should be default in calc.

Martin S gravatar imageMartin S ( 2020-08-18 19:13:21 +0100 )edit
1

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

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 +0100 )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 +0100 )edit

Hallo

SUBSTITUTEs "1" by "" ( empty strings )

karolus gravatar imagekarolus ( 2016-03-17 21:48:51 +0100 )edit

Question Tools

2 followers

Stats

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

Seen: 4,032 times

Last updated: Mar 17 '16