Copy last 4 chars from Excel cell to the next column

[stev379]

How can I read a column in an Excel file and copy or cut the last 4 characters of column A into column B? 

I need to pull the year date to its own column so I can sort the rows by year. I have 1910 rows. 

Cell A:1 contents:

Thu May 24 11:39:25 EDT 2012

It looks like _Excel_RangeRead is what I'll need, but I'm stuck on pulling the data for each cell before the array displays or yanking it out of the array.

This all I have  so far ...and it ain't right. It's 99% a copy from the Help file with my file path added. I can't find the old functions that I used to read and loop each cell in my old script. I added the 2nd to last line below. If it will loop and read each cell in column A, then I can handle the parsing and likely the writing to the next column.

 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\WDEActivityReport-1910-062116.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook" & @ScriptDir & "\WDEActivityReport-1910-062116.xlsx" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Read the formulas of a cell range (all used cells in column A)
; *****************************************************************************
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Data successfully read." & @CRLF & "Please click 'OK' to display all formulas in column A.")

MsgBox(0,'', "Cell A:1 = " & $oWorkbook.ActiveSheet("A:1"))

;_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A")

 

[JLogan3o13]

@stev379 you can loop through the array like this MsgBox example. 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsx")
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

    For $i = 0 To UBound($aResult) - 1
        MsgBox(0,'', "Cell A:" & $i + 1 & ", " & $aResult[$i])
    Next

If you need the last four characters, you could do something like this:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsx")
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

    For $i = 0 To UBound($aResult) - 1
        _Excel_RangeWrite($oWorkbook, Default, StringRight($aResult[$i], 4), "B" & $i + 1)
    Next

Although one of our Regex gurus will probably wander along to give you another method

Edited June 22, 2016 by JLogan3o13

[stev379]

THANK YOU! I used the 2nd one and it worked perfectly the first time. This is a huge help with this audit. 

You are the man!

[JLogan3o13]

Glad it worked for you