How to Extract 4-Digit Numbers Preceded by a Pound Symbol in Excel

To extract the 4-digit numbers preceded by the pound symbol (#) from the given sample data, we can use a combination of Excel functions and a custom VBA function. Here's a step-by-step guide to achieve this:

1. Prepare the data in Excel:

Apple #1000
Red Potato #2000
Brown Laptop #3000
Black Printer #4000
White Table #5000
Sky #6000
Blue

2. Insert a new column next to the data column (column A) and name it "Numbers".

3. In cell B2 (assuming the data starts in cell A2), enter the following formula:

=IFERROR(SEARCH("#", A2) + SEARCH(" ", A2, SEARCH("#", A2) + 1) - SEARCH("#", A2), "")

4. Copy the formula from cell B2 to the rest of the cells in the "Numbers" column (column B).

5. In cell C2, enter the following formula:

=IFERROR(RIGHT(A2, 4), "")

6. Copy the formula from cell C2 to the rest of the cells in the "Numbers" column (column C).

7. Now, the 4-digit numbers preceded by the pound symbol are in column C.

If you want to convert this Excel table into plain HTML, you can follow these steps:

1. Save the Excel file as an HTML file (File > Save As > Web Page).

However, if you need to extract the numbers programmatically using VBA, you can use the following code:

Sub ExtractNumbers()

    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long
    Dim numbers As Variant

    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name if needed
    Set rng = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))

    numbers = Array()

    For i = 1 To rng.Cells.Count
        If rng.Cells(i, 1).Value Like "#* * * *" Then
            numbers = VBA.ArrayUnion(numbers, WorksheetFunction.Right(rng.Cells(i, 1).Value, 4))
        End If
    Next i

    Debug.Print Join(numbers, ", ")

End Sub

This code will extract the 4-digit numbers preceded by the pound symbol from the specified worksheet and print them as a comma-separated list.