Excel Formula to Concatenate and Convert Two Columns to Uppercase

Manually merging text columns while isolating only uppercase letters is a tedious, error-prone hurdle for data analysts seeking clean datasets. While standard funding sources for enterprise IT modernization often overlook these daily operational inefficiencies, mastering advanced spreadsheet formulas grants users immediate data-formatting autonomy without the need for expensive software procurement.

However, note the stipulation: native Excel lacks a direct "IsUpper" filter, requiring a strategic, nested array approach to isolate case-sensitive characters. Organizations successfully leverage this method for consolidating disparate regional SKU codes across global departments. Below, we will break down the exact combination of TEXTJOIN, MID, and EXACT formulas to automate this concatenation.

Introduction

Data cleaning and preparation are some of the most common tasks performed in Excel. Often, you may find yourself needing to extract specific patterns of text from your datasets to generate unique keys, IDs, abbreviations, or initials. One particularly common requirement is extracting and concatenating only the uppercase letters from two different columns.

For example, if Column A contains "John DOE" and Column B contains "Sales Manager", you might want to extract the uppercase letters to create an identifier like "JDOESM". Because Excel does not have a built-in EXTRACTUPPERCASE function, achieving this requires combining several functions to inspect each character, check if it is uppercase, and join them back together.

In this comprehensive guide, we will explore the best methods to concatenate uppercase letters from two columns, ranging from modern Excel 365 formulas to traditional array formulas, Power Query, and VBA solutions.

The Goal: What We Are Building

Before diving into the formulas, let's visualize the expected output. Suppose we have a table with two columns, and we want to extract only the uppercase letters (A-Z) from both, merging them into a single string:

Method 1: The Modern Excel 365 Formula (Best & Easiest)

If you are using Excel 365 or Excel 2021, you have access to powerful dynamic array functions like LET, MAP, SEQUENCE, and LAMBDA. These functions make it incredibly straightforward to split a string into individual characters, filter them based on their character codes, and join them back together.

The Formula

=LET(
    combinedString, A2 & B2,
    stringLength, LEN(combinedString),
    characters, MID(combinedString, SEQUENCE(stringLength), 1),
    TEXTJOIN("", TRUE, MAP(characters, LAMBDA(char, IF(AND(CODE(char) >= 65, CODE(char) <= 90), char, ""))))
)

How It Works

1. combinedString, A2 & B2: First, we use the LET function to define variables. We concatenate the text in cells A2 and B2 into a single string.
2. stringLength, LEN(combinedString): We calculate the total length of this combined string.
3. characters, MID(...): Using SEQUENCE(stringLength), we generate an array of numbers from 1 to the length of the string. The MID function uses this array to split our combined string into an array of individual characters.
4. MAP(characters, LAMBDA(...)): The MAP function inspects each individual character (represented as char).
5. CODE(char): This is the secret to case sensitivity in Excel. The CODE function returns the numeric ASCII value of a character. In ASCII, uppercase letters 'A' through 'Z' range from 65 to 90. Lowercase letters, spaces, numbers, and special characters fall outside this range.
6. IF(AND(CODE(char) >= 65, CODE(char) <= 90), char, ""): If the character's ASCII code is between 65 and 90, we keep it; otherwise, we replace it with an empty string ("").
7. TEXTJOIN("", TRUE, ...): Finally, TEXTJOIN merges the filtered array of characters back into a single string, skipping any empty cells.

Method 2: The Classic Array Formula (For Excel 2019 & 2016)

If you are using an older version of Excel that supports TEXTJOIN but does not support dynamic arrays like MAP or LAMBDA, you can use a classic array formula.

The Formula

Enter the following formula and press Ctrl + Shift + Enter (if you are not on Excel 365):

=TEXTJOIN("", TRUE, IF(ISNUMBER(MATCH(CODE(MID(A2&B2, ROW(INDIRECT("1:"&LEN(A2&B2))), 1)), ROW(INDIRECT("65:90")), 0)), MID(A2&B2, ROW(INDIRECT("1:"&LEN(A2&B2))), 1), ""))

How It Works

• ROW(INDIRECT("1:"&LEN(A2&B2))) generates a static array of numbers corresponding to the position of each character.
• MID(A2&B2, ..., 1) extracts each character one by one.
• CODE(...) converts each character to its ASCII code.
• MATCH(..., ROW(INDIRECT("65:90")), 0) checks if the ASCII code matches any number in the range of 65 to 90 (the ASCII values for A-Z).
• If a match is found, the IF function returns that character; otherwise, it returns an empty string.
• TEXTJOIN glues the matching uppercase characters together.

Method 3: Using Power Query (No-Code, Best for Large Datasets)

If you are working with large datasets containing thousands of rows, executing complex array formulas can slow down your workbook. Power Query is the most efficient tool for this scenario. It provides a clean, visual way to manipulate text without writing long formulas.

Step-by-Step Guide

1. Select your data range, navigate to the Data tab, and click From Table/Range to open the Power Query Editor.
2. Select both Column A and Column B, right-click, and select Merge Columns. Choose "None" as the separator and name the column (e.g., Merged).
3. Go to the Add Column tab and select Custom Column.
4. In the Custom Column formula box, paste the following Power Query M code:

   Text.Select([Merged], {"A".."Z"})

5. Click OK. Power Query will instantly extract only the uppercase letters.
6. You can now delete the temporary Merged column.
7. Go to Home > Close & Load to return the cleaned data back to your Excel worksheet.

Method 4: User-Defined VBA Function (For Legacy Excel 2013 and Older)

If you are working on a very old version of Excel that lacks the TEXTJOIN function entirely, your best option is to write a short User-Defined Function (UDF) using VBA.

The VBA Code

To add this code to your workbook:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code into the module:

Function ConcatenateUpper(cell1 As Range, cell2 As Range) As String
    Dim combined As String
    Dim i As Long
    Dim charCode As Integer
    Dim result As String
    
    combined = cell1.Value & cell2.Value
    result = ""
    
    For i = 1 To Len(combined)
        charCode = Asc(Mid(combined, i, 1))
        ' ASCII 65 to 90 represents uppercase letters A to Z
        If charCode >= 65 And charCode <= 90 Then
            result = result & Mid(combined, i, 1)
        End If
    Next i
    
    ConcatenateUpper = result
End Function

How to Use It in Excel

Once you close the VBA window, you can use this function just like a regular Excel formula:

=ConcatenateUpper(A2, B2)

Comparing the Methods

To help you choose the best option for your specific setup, here is a quick breakdown of each method:

Conclusion

Extracting and concatenating uppercase letters from two columns is a highly effective way to parse messy text data into clean, structured abbreviations or identifiers. If you are on Excel 365, the LET and MAP combination is the cleanest, most responsive solution available. For legacy versions or massive datasets, Power Query and VBA provide reliable, high-performance alternatives.