请求编写VBA代码从firstname.lastname@email.com格式邮箱提取名与姓

Got it, let's convert those Excel formulas into a clean, reusable VBA subroutine that you can add to your existing module library. This will automatically extract first names to column B (starting at B2) and last names to column C (starting at C2) from the email addresses in column A.

VBA Code Implementation #

Sub ExtractNamesFromEmail()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim emailStr As String
    Dim dotPos As Integer
    Dim atPos As Integer
    Dim i As Long
    
    ' Set the worksheet to the active sheet (change to specific sheet name if needed)
    Set ws = ActiveSheet
    
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 2
    For i = 2 To lastRow
        emailStr = ws.Cells(i, "A").Value
        
        ' Skip empty cells
        If emailStr = "" Then GoTo NextRow
        
        ' Find positions of dot and @ symbol
        dotPos = InStr(emailStr, ".")
        atPos = InStr(emailStr, "@")
        
        ' Check if the email has the expected format
        If dotPos > 0 And atPos > dotPos Then
            ' Extract first name (before the dot)
            ws.Cells(i, "B").Value = Left(emailStr, dotPos - 1)
            ' Extract last name (between dot and @)
            ws.Cells(i, "C").Value = Mid(emailStr, dotPos + 1, atPos - dotPos - 1)
        Else
            ' Mark invalid format if needed
            ws.Cells(i, "B").Value = "Invalid format"
            ws.Cells(i, "C").Value = "Invalid format"
        End If
        
NextRow:
    Next i
    
    MsgBox "Name extraction completed!", vbInformation
End Sub

代码说明 #

• Worksheet Setup: The code uses the active worksheet by default—if you need to target a specific sheet, replace ActiveSheet with ThisWorkbook.Worksheets("YourSheetName").
• Last Row Detection: It automatically finds the last row with data in column A, so you don't have to hardcode row numbers.
• Format Validation: Adds a check to make sure the email has both a dot (.) and an @ symbol (with the dot coming before the @), so it won't throw errors for malformed emails.
• Name Extraction:
  • First name: Uses Left to get everything before the dot, matching your original formula's logic exactly.
  • Last name: Uses Mid to grab the text between the dot and the @ symbol—this is a more concise alternative to nesting RIGHT and LEFT like your original formula, but it achieves the same result.
• Error Handling: Skips empty cells and marks invalid email formats so you can easily spot issues in your data.

To use this:

1. Open your Excel file
2. Press Alt + F11 to open the VBA Editor
3. Insert a new module (right-click your workbook in the Project Explorer > Insert > Module)
4. Paste the code above into the module
5. Run the subroutine (you can add it to your macro list for quick access later)

内容的提问来源于stack exchange，提问作者ExcelvbaNovice