Excel中批量去除SKU末尾字母的实现方法咨询

Got it, let's solve this problem where you need to strip all trailing alphabetic characters from your SKUs in column A and output the result to column C. Below are three practical methods tailored to different needs:

Method 1: Excel Formula (No-Code, Quick for Small Datasets) #

If you prefer not to use code, these formulas will get the job done:

For Excel 365/2021 (Dynamic Array Support) #

Use the TEXTBEFORE function to target the last occurrence of any uppercase letter:

=TEXTBEFORE(A1,{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},-1,TRUE)

• -1 tells the function to find the last match of any letter in the array
• TRUE makes it case-insensitive (works if your SKUs have lowercase letters too)

For Older Excel Versions (Pre-365/2021) #

Use an array formula to find the position of the last numeric character, then truncate the SKU to that position:

=LEFT(A1, MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))

• After typing the formula, press Ctrl+Shift+Enter (not just Enter) to activate the array calculation.

Method 2: VBA Macro (Batch Processing for Large Datasets) #

If you have hundreds/thousands of SKUs or need to repeat this task often, a VBA macro will save time:

1. Press Alt+F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer > Insert > Module
3. Paste this code:

Sub StripTrailingLetters()
    Dim targetRange As Range
    Dim cell As Range
    Dim skuText As String
    Dim charPosition As Integer
    
    ' Define the range (A1 to last filled cell in column A)
    Set targetRange = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    For Each cell In targetRange
        skuText = cell.Value
        charPosition = Len(skuText)
        
        ' Loop backward from the end to find the first non-letter character
        Do While charPosition > 0 And (Asc(UCase(Mid(skuText, charPosition, 1))) >= 65 And Asc(UCase(Mid(skuText, charPosition, 1))) <= 90)
            charPosition = charPosition - 1
        Loop
        
        ' Write the result to column C (2 columns to the right)
        cell.Offset(0, 2).Value = Left(skuText, charPosition)
    Next cell
End Sub

4. Press F5 to run the macro, or assign it to a button for easy access later.

Method 3: Power Query (For Data Transformation Workflows) #

If you're working with structured data and want a repeatable, non-destructive workflow:

1. Select your data in column A
2. Go to the Data tab > Click From Table/Range (check "My table has headers" if you have a header row)
3. In the Power Query Editor:
   • Go to Add Column > Custom Column
   • Paste this formula in the custom column editor:

     =Text.BeforeDelimiter([Column1], Text.Select([Column1], {"A".."Z"}), {0, RelativePosition.FromEnd})
     

   • Rename the custom column to something like "Clean SKU"
4. Click Close & Load To > Choose to load the data to column C (or a new sheet, then copy over)

内容的提问来源于stack exchange，提问作者Jun Young Kim