Incrementing a Series of Integers Stored as a Text Value

Jose has a worksheet that has four comma-separated integers in column A, such as 5,6,9,13. These, obviously, are stored as text. There are hundreds of rows of values like this in the column. Jose wonders if there is a formula he can place in column B that will increment each integer, such as 6,7,10,14. He'd prefer to avoid separating the digits out to individual columns, if possible.

The answer to this depends largely on the version of Excel you are using. Or, I should say, the complexity of the answer depends on the version. If you want a formula that does not rely on functions in the newer versions of Excel, then you can use this in cell B1:

=LEFT(A1,FIND(",",A1)-1)+1 & "," &
MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1)+1 & "," &
MID(A1,FIND(",",A1,FIND(",",A1)+1)+1,FIND(",",A1,FIND(",",A1,FIND(",",A1)+1)+1)-FIND(",",A1,FIND(",",A1)+1)-1)+1 & "," &
RIGHT(A1,LEN(A1)-FIND(",",A1,FIND(",",A1,FIND(",",A1)+1)+1))+1

I've shown the formula here as four lines, but remember that it is all a single formula, entered on a single line. I broke it into four lines so it was more easily seen what it takes to increment the four numbers. (Each line is responsible for a single digit.) The FIND function is used to determine the location of each comma. The LEFT function is used to pull out the first number, the MID function the second and third numbers, and the RIGHT function the fourth numbers. Add 1 to each of the numbers forces Excel to treat the numbers as numeric values, and then the concatenation operator (&) forces the incremented numeric values back to text.

The drawback to the formula should be obvious—it is amazingly long. Plus, it will only work if there are four numbers separated by three commas. If there are more or less, then it will not work properly.

To get around these drawbacks, a macro-based solution was required. The following is an example of a macro that will easily pull apart any sequence of comma-separated values and return an incremented sequence:

Function IncrTxt(ByRef rng As Range, Optional iIncr As Integer)
    Dim var As Variant
    Dim str As String
    Dim i As Integer

    Application.Volatile
    If iIncr = 0 Then iIncr = 1
    var = Split(rng.Value, ",")
    For i = LBound(var) To UBound(var)
        str = str & var(i) + iIncr & ","
    Next i
    IncrTxt = Left(str, Len(str) - 1)
End Function

This is a user-defined function that can be placed into cell B1 in the following manner:

=IncrTxt(A1,1)

The second parameter is optional. It can be used if you want to increment the numbers by something other than 1.

Everything discussed so far will work in all versions of Excel. If you are using the latest versions, however, you have access to worksheet functions that can make the task a snap. Here's a simple formula you could use in B1 to do the job:

=TEXTJOIN(",",,TEXTSPLIT(A1,",")+1)

Then, copy the formula down for as many rows as necessary. The formula uses the TEXTSPLIT function to create an array of values by splitting the text string based on the commas. Each value is incremented by 1, and then TEXTJOIN is used to create a new string based on the incremented values separated by commas. This formula will only work for those using Excel 2024 or Microsoft 365 because those are the versions in which the TEXTSPLIT function is available.

Some may find this variation of the formula helpful:

=TEXTJOIN(",",,MAP(TEXTSPLIT(A1,","),LAMBDA(n,n+1)))

This formula relies on the MAP and LAMBDA functions to specify what should happen to each element of the array created by TEXTSPLIT. LAMBDA specifics that the value should be incremented by 1. To me, though, using MAP and LAMBDA for a simple increment seems to be a bit of overkill. It would be a more powerful approach, though, if Jose wanted something more complex done to each of the comma-separated numbers in column A. (The "more complex" concept will be illustrated in a moment in another use of LAMBDA.)

If you prefer to not use TEXTJOIN for some reason, you could instead use ARRAYTOTEXT, in this manner:

=ARRAYTOTEXT(TEXTSPLIT(A1,",")+1)

Note that ARRAYTOTEXT, in creating a new text string, automatically separates values by commas, while TEXTJOIN required the explicit specification of the delimiter to be used between values. The biggest difference, though, is that TEXTJOIN doesn't include a leading space for each number in the result whereas ARRAYTOTEXT does.

Note that these three formulas do not care how many comma-separated numbers are in the strings in column A. The formulas will work fine as long as there is at least one number in the cell in column A and if there are multiple numbers they are separated by only one comma. (In other words, the cell in column A cannot be blank and it cannot contain something like 5,6,,13 with two commas separating numbers.)

If you are absolutely sure that the strings in column A will always have four numbers separated by three commas, then you might want to consider this formula in cell B1:

=BYROW(TEXTSPLIT(TEXTJOIN("|",,A:A),",","|")+1,LAMBDA(row,TEXTJOIN(",",,row)))

This formula is more complex and therefore requires more explanation. It uses the first instance of TEXTJOIN to combine all the values in column A into a single string where each row is separated by a "|" character.

Using this super-long string of values, TEXTSPLIT is used to create a 4xN array of the numbers in column A. Each row in the array is defined by the "|" character. Essentially you end up with an array that is 4 columns by however many rows there are in column A. If any of the rows in column A are blank, then that row is ignored. Each of the values in the array created by TEXTSPLIT is then incremented by 1.

Finally, the BYROW function is used to apply a function to each row of the array returned by TEXTSPLIT. What function is it that is applied? In this case, the function is defined by the LAMBDA function which uses the second instance of TEXTJOIN to combine each of the 4 values in the array row being processed by BYROW. The BYROW function, after applying the LAMBDA function, returns a single row. Thus, each row spills over to a new worksheet row automatically.

This formula is much more complex than the earlier ones, but the benefit is that it processes everything in column A at once (you don't need to copy the formula downward) and it ignores any blank cells in column A.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9950) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.