Run text to columns with a fixed width in column A then remove duplicates

Question

I have a CSV file that has data in column A.

I need to separate the text in columns and remove the duplicates.

The output should look like this:

I only care about keeping the three digit number in column A and removing those duplicates. All other data can be discarded.

I plan on running the script by adding a button to the worksheet.

I tried using the automation macro within Excel, and it works, but I need to add the functionality for all users for all copies of the workbook.

Here is the code from the automation macro:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Text to columns on range A1:A300 on selectedSheet
    for (let row = 0; row < selectedSheet.getRange("A1:A300").getRowCount(); row++) {
        let sourceRange: ExcelScript.Range;
        let destinationRange: ExcelScript.Range;
        let sourceRangeValues: string;
        let destinationValues: string[];
        let previousValue: number;
        sourceRange = selectedSheet.getRange("A1:A300");
        destinationRange = selectedSheet.getRange("A1");
        sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString()
        destinationValues = [];
        previousValue = 0;
        for (let i = 0; i < 1; i++) {
          const cur = [3][i];
          destinationValues.push(sourceRangeValues.substring(previousValue, cur))
          previousValue = cur;
        }
        destinationValues.push(sourceRangeValues.substring(previousValue));
        destinationRange.getOffsetRange(row, 0).getResizedRange(0, destinationValues.length - 1).setValues([destinationValues]);
    }
    // Remove duplicates from range A1:A114 on selectedSheet
    selectedSheet.getRange("A1:A300").removeDuplicates([0], false);
}

Pasting that into VBA didn't work.

Sub txt2col_remdup()
    Range("A1:A300").TextToColumns
    Range("A1:A300").RemoveDuplicates
End Sub

Answer 1

Please try.

Sub Macro2()
    Dim r As Range
    Set r = Range("A1").CurrentRegion
    r.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(3, 9)), TrailingMinusNumbers:=True
    r.RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Microsoft documentation:

XlColumnDataType enumeration (Excel)

Name	Value	Description
xlSkipColumn	9	Column is not parsed.
xlTextFormat	2	Text.

---

Dictionary object is another option to get it done.

Sub Demo()
    Dim objDic As Object, rngData As Range
    Dim i As Long, sKey As String
    Dim arrData
    Set objDic = CreateObject("scripting.dictionary")
    Set rngData = Range("A1").CurrentRegion
    arrData = rngData.Value
    For i = LBound(arrData) To UBound(arrData)
        sKey = "'" & Left(arrData(i, 1), 3)
        objDic(sKey) = ""
    Next i
    rngData.Clear
    Range("A1").Resize(objDic.Count, 1) = Application.Transpose(objDic.keys)
    Set objDic = Nothing
End Sub