Gå til hovedindhold

Hvordan kan man automatisk øge bogstavet med et for at få det næste bogstav i Excel?

Hvis du antager, at du har en celle, der indeholder bogstavet AB, skal du øge det sidste bogstav med et for at få det næste bogstav AC, AD ... og så videre. Eller du har et bogstav A og vil øge bogstavet til B, C ... for at oprette en bogstavliste. Hvordan opnås det? Denne artikel kan hjælpe dig i detaljer.

Forøg bogstavet AB med en for at få AC, AD ... med formlen
Forøg bogstavet med et for at få bogstavlisten med formlen
Forøg bogstavet med et for at få bogstavlisten med brugerdefineret funktion


Forøg bogstavet AB med en for at få AC, AD ... med formlen

Gør som følger for at øge bogstavet med et i Excel.

1. Vælg en tom celle under det givne bogstav, indtast nedenstående formel i den, og tryk derefter på Indtast nøgle. 

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

Bemærk: I formlen er A2 cellen indeholder det givne bogstav.

2. Bliv ved med at vælge A3, træk Fill Handle ned for at få listen over bogstaver, du har brug for.


Forøg bogstavet A med et for at få bogstavlisten med formlen

Du kan bruge = CHAR (KODE (A1) +1) formel for at øge bogstavet med et i Excel. Gør som følger.

1. Vælg en tom celle under det givne bogstav, indtast formlen = CHAR (KODE (A2) +1) ind i formellinjen, og tryk derefter på Indtast nøgle. Se skærmbillede:

2. Vælg resultatcellen, træk Fill Handle ned for at få det ønskede bogstav.

Bemærk: Med denne formel får du efter at have fået bogstavet Z, hvis du fortsætter med at trække Fill Handle ned, nogle specialtegn som vist nedenfor. Hvis du ønsker at få bogstavet AA efter Z, kan følgende brugerdefinerede funktion hjælpe dig.


Forøg bogstavet med et for at få bogstavlisten med brugerdefineret funktion

Den følgende brugerdefinerede funktion kan også hjælpe dig med at øge bogstavet med et i Excel.

1. Trykke andre + F11 taster samtidigt for at åbne Microsoft Visual Basic til applikationer vindue.

2. i Microsoft Visual Basic til applikationer vindue, skal du klikke på indsatte > Moduler. Kopier og indsæt derefter nedenstående VBA-kode i Moduler vindue.

VBA-kode: Forøg bogstavet med et for at få bogstavlisten

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

3. Trykke andre + Q taster for at lukke Microsoft Visual Basic til applikationer vindue.

4. Vælg en tom celle under det givne bogstav, indtast formlen = ColLtrs (ROW ()), og tryk derefter på Indtast tast for at få det andet bogstav.

2. Vælg resultatcellen, træk Fill Handle ned for at få det ønskede bogstav.

Bemærk: Med denne metode er bogstaver, du har øget, baseret på række nummer. Du skal starte det første bogstav A i første række af regnearket, ellers bliver brevet uordnet.

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier    Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar    Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler    Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse


Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Comments (21)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
is there any formula to increase or write ascending order like 1nc1, 1nc2, 1nc3.....
This comment was minimized by the moderator on the site
Hi,

To create an incrementing sequence like "1nc1", "1nc2", "1nc3", etc., in Excel, you can use a formula that leverages the row number to generate the numeric part of the sequence.

Assuming you want to start this sequence in cell A1, you would enter the following formula in A1:
="1nc" & ROW(A1)
Then, drag this formula down to the desired number of rows. Each cell will generate a part of the sequence based on its row number. For example, cell A2 will display “1nc2”, A3 will display “1nc3”, and so on.

The logic behind this formula is:
ROW(A1) returns the row number of cell A1, which is 1.
"1nc" & ROW(A1) concatenates the string “1nc” with the row number, creating “1nc1”.
This comment was minimized by the moderator on the site
I need a code to help make my rows from 1-A to 1-Z please
This comment was minimized by the moderator on the site
Hi Mayra Lopez,
The VBA code provided in the post is still helpful, but you need to apply the following formula to get the results you need:
="1-"&ColLtrs(ROW())
This comment was minimized by the moderator on the site
How would I create a formula that advances like this?:

AAAAAAAAA
AAAAAAAAB
AAAAAAAAC
AAAAAAAAD
etc.
This comment was minimized by the moderator on the site
Hi Willie B,
You can try the VBA code provided in the post. After adding the code, use this formula to get the result you need:
="AAAAAAAA"&ColLtrs(ROW())
This comment was minimized by the moderator on the site
I need a code for increasing my row cell values from AA-01-A01, AA-01-A02 to AA-01-B01, AA-01-B02. And I also need a code for my colomn cell values from AA-01-A01, AA-01-A02 to AA-03-A01, A-03-A02. Help.
This comment was minimized by the moderator on the site
I need a code from A-Z, AA-ZZ, AAA to ZZZ, AAAA to ZZZZ
This comment was minimized by the moderator on the site
Hi,Sorry can't help with this problem. You can post the problem to the forum below to get help from other Excel enthusiasts.
https://www.extendoffice.com/forum/kutools-for-excel.html 
This comment was minimized by the moderator on the site
How do I get it to start over at A when Z is used?
This comment was minimized by the moderator on the site
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))))
This comment was minimized by the moderator on the site
Thank you Zac for the letter sequence formula, saved me a ton load of work
This comment was minimized by the moderator on the site
Is there a way to do this for entire words? Where every letter in the word moves up by X number of characters
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
Everything man does is corrupt. Everything.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations