Kako samodejno povečati črko za eno, da dobite naslednjo črko v Excelu?

Če imate celico, ki vsebuje črko AB, morate zadnjo črko povečati za eno, da dobite naslednjo črko AC, AD ... itd. Ali imate črko A in jo želite povečati na B, C…, da ustvarite seznam črk. Kako to doseči? Ta članek vam lahko pomaga pri podrobnostih.

Povečajte črko AB za eno, da dobite AC, AD ... s formulo
Povečajte črko za eno, da dobite seznam črk s formulo
Povečajte črko za eno, da dobite seznam črk z uporabniško določeno funkcijo

Povečajte črko AB za eno, da dobite AC, AD ... s formulo

Naredite naslednje, če želite v Excelu povečati črko za eno.

1. Izberite prazno celico pod dano črko, vanjo vnesite spodnjo formulo in pritisnite tipko Vnesite ključ.

=IF(RIGHT($A2,1)="Z", CHAR(CODE(LEFT(A2,1))+1),LEFT(A2,1))&CHAR(65+MOD(CODE(RIGHT(A2,1))+1-65,26))

Opombe: V formuli je A2 celica, ki vsebuje dano črko.

2. Nadaljujte z izbiro A3, povlecite ročico za polnjenje navzdol, da dobite seznam črk, ki jih potrebujete.

Povečajte črko A za eno, da dobite seznam črk s formulo

Lahko uporabite = CHAR (KODA (A1) +1) formula za povečanje črke za eno v Excelu. Naredite naslednje.

1. Izberite prazno celico pod dano črko, vnesite formulo = CHAR (KODA (A2) +1) v vrstico formule in nato pritisnite Vnesite tipko. Oglejte si posnetek zaslona:

2. Izberite celico z rezultatom, povlecite ročico za polnjenje navzdol, da dobite črko, ki jo potrebujete.

Opombe: Če boste še naprej vlekli ročico za polnjenje navzdol, boste po tej formuli dobili črko Z in dobili nekaj posebnih znakov, kot je prikazano spodaj Če želite po črki Z dobiti črko AA, vam lahko pomaga naslednja uporabniško določena funkcija.

Povečajte črko za eno, da dobite seznam črk z uporabniško določeno funkcijo

Naslednja uporabniško določena funkcija vam lahko pomaga tudi, da v Excelu povečate črko za eno.

1. Pritisnite druga + F11 tipke hkrati, da odprete Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli. Nato kopirajte in prilepite spodnjo kodo VBA v Moduli okno.

Koda VBA: Povečajte črko za eno, da dobite seznam črk

Function ColLtrs(i As Long) As String
    ColLtrs = Replace(Cells(1, i).Address(False, False), "1", "")
End Function

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Izberite prazno celico pod dano črko, vnesite formulo = ColLtrs (ROW ())in nato pritisnite Vnesite tipko, da dobite drugo črko.

2. Izberite celico z rezultatom, povlecite ročico za polnjenje navzdol, da dobite črko, ki jo potrebujete.

Opombe: Pri tej metodi povečate črke glede na številko vrstice. Prvo črko A morate začeti v prvi vrstici delovnega lista, sicer bo črka neurejena.

Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča produktivnost za 80%

Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2019 in 365. Podpira vse jezike. Preprosta namestitev v vašem podjetju ali organizaciji. Vse funkcije 30-dnevnega brezplačnega preskusa. 60-dnevno jamstvo za vračilo denarja.

Preberite več ... Brezplačen prenos... Nakup ...

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
Poveča vašo produktivnost za 50% in vsak dan zmanjša na stotine klikov z miško!

Preberite več ... Brezplačen prenos... Nakup ...

13 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
brickh7777@gmail.com · 2 years ago
How do I get it to start over at A when Z is used?
- Reply
To post as a guest, your comment is unpublished.
Zac · 2 years ago
This code will allow you to go from A to ZZ.

=IF(B2="Z","AA",IF(LEN(B2)=1,CHAR(CODE(B2)+1),IF(RIGHT(B2,1)="Z",CHAR(CODE(LEFT(B2,1))+1),LEFT(B2,1))&CHAR(65+MOD(CODE(RIGHT(B2,1))+1-65,26))))
- Reply
To post as a guest, your comment is unpublished.
Dan · 2 years ago
Is there a way to do this for entire words? Where every letter in the word moves up by X number of characters
- Reply
To post as a guest, your comment is unpublished.
Harold McBroom · 2 years ago
Excel was the spreadsheet that superseded Lotus 1-2-3, and having been around that long, you would think that geniuses at Microsoft would have created a more simple means of retrieving a cells column number, without having to write your own function. Back in 2001, 2002 when I was programming in VBA there were only 65536 rows in an Excel spreadsheet, now the number has increased to a ridiculous 1.4 million rows. It's not how big the database is that counts, but how intelligent you manage the data on your hard drive, when accessing that data through Excel.

If that doesn't make you upset, then the Bankers Rounding scheme used by Excel should finish the job! We were taught math in school, basic rounding, come to find out, the Bankers "guild" wants to determine how cash amounts are rounded to better cater to their advantages. For instance, 1.5 is either closer to 1 or 2, but is rounded up to 2, being the even number. 0.5 is either closer to 0 or 1, but being the odd number, they round it down to 0. The entire system is corrupt, and they create the business software used by American businesses.

I read a comment below the one I'm addressing, and someone basically said, "I have a lot of banker friends, and they never heard of Bankers Rounding; They say they used a method called "5 Step Rounding" in the EU. So does that mean the Bankers Rounding is used only in the sabotage of the American Economy, but not good enough to ruin the European Economy that's trying to take over the world?! These money changers never cease to amaze me at how far they will go in their arrogance!
- Reply
- To post as a guest, your comment is unpublished.
  Me · 2 years ago
  
  Everything man does is corrupt. Everything.
  
  Reply
To post as a guest, your comment is unpublished.
afzalss · 3 years ago
Dear Sir
When I run the function =ColLtrs(ROW()) in an excel sheet, I get perfect results, but if I want to call this function or cell value in VBA, then is it possible. So my variable could be i with values 1,2,3,4,....27,28, etc and result should be A,B,C,D....,AA,AB and so on. Is it possible?
Thanks
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  Please select the number cells and run the below code to solve the problem.
  
  Sub NumTOAZ()
  Dim xRg As Range
  Dim xStr As String
  On Error Resume Next
  For Each xRg In Selection
  xStr = Replace(Cells(1, xRg.Value).Address(False, False), "1", "")
  xRg.Value = xStr
  Next
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
Afzal · 3 years ago
the formula =ColLtrs(ROW()) works perfectly well in an excel cell, I need to use the same inside a VBA to give me result from A to Z then AA to AZ and so on. If I use CHAR in VBA it has limitation to go only till Z.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  Way not keep using the VBA code? The above VBA code can create letters from A to Z then AA to AZ and so on to meet your needs.
  
  Reply
To post as a guest, your comment is unpublished.
cdarzur · 3 years ago
thank you for the above. But what if you want to increment from 400_A to 400_B? I tried a few things but I can only make it work with numbers.
Thanks
- Reply
- To post as a guest, your comment is unpublished.
  cdarzur · 3 years ago
  
  Found the answer. Thank you.
  =LEFT(B1,4)&"_"&CHAR(CODE(RIGHT(B1,1))+1)
  
  Reply

Prijava na vaš račun

Ponastavitev gesla