Techniques for Increasing a String from ‘aa’ to ‘ab’

To avoid allocating numerous new objects, it is recommended to use an array of characters instead and repeat the process until a non-Z character is found. For the solution, enter an arbitrary starting value in cell A1 and copy it across as required. For propagating a string like “JAMES” in VBA, consider using individual characters instead of complete strings.


Solution 1:

Place the text “AA” in cell A1 and then enter the given formula into cell B1. After that, drag the formula across the cells.

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

The sequence will continue to increase in the following manner: AA, AB, AC,…, AZ, BA, BB, BC…. and so on.

To customize your presentation, you may need to modify this equation. It’s important to keep in mind that its applicability ends at “ZZ”.

Bug has been resolved through an update.


Solution 2:


The Excel spreadsheet can be utilized to increment the letters from

A

to

XFC

.

To begin, establish the cell reference as

INDIRECT(A1&"1")

. Afterward, determine the location of the adjacent column, referred to as

ADDRESS(1,COLUMN(INDIRECT(A10&"1"))+1)

.

We can extract the letters from the $??$1 in two different ways.

  1. Look for the second $, and snip the text out between them

    =MID(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1),2,FIND("$",ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1),2)-2)
     
  2. Replace the 1 and $ with nothing in the string

    =SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1),"$",""),"1","")
     

Choose which one works best for you


Solution 3:


Copy the formula written in cell A1 to replicate it in one or more cells. For instance, you can try this with another example.

=CHAR(MOD(ROW(A1)-1;26)+65)&CHAR(MOD(COLUMN(A1)-1;26)+65)

This is meant to serve as a demonstration of a potential thought process for approaching the issue.

Frequently Asked Questions