• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey

Excel & AutoHotkey-Replacing Null values in Excel

Replacing Null values in ExcelAutoHotkey Bottle 2When some programs export data they will insert #Null! into a cell to indicate there was no data (SPSS to name one).  While I would rather have something than nothing, when trying to compute averages or share data with clients the #Null! value is not ideal.  🙁  In this tutorial I demonstrate how easy it is to Replacing Null values in Excel with AutoHotkey.

The following video demonstrates Replacing Null values in Excel.  While I didn’t show the entire code in the video, it is below for your reference.

AutoHotkey Merchandise-White Stress ball

AutoHotkey Function for Replacing Null values in Excel

;***********get handle*******************
XL_Handle(XL,1) ; pointer to Application   XL_Handle(XL,2) Pointer to Workbook

;***********call function *******************

;***********replace Null*******************
if (! RG)
PXL.Range(RG).Replace("#NULL!","")  ;

;***********************Used Range********************************.
;~ XL_Used_RG(XL,Header:=1) ;Use header to include/skip first row
IfEqual,Header,0,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL) ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL)
IfEqual,Header,1,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL)+1 ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL)

;***********************First row********************************.
;~ XL_First_Row(XL)
   Return, PXL.Application.ActiveSheet.UsedRange.Rows(1).Row

;***********************Last Row********************************.
;~ XL_Last_Row(XL)
   Return, PXL.Application.ActiveSheet.UsedRange.Rows(PXL.Application.ActiveSheet.UsedRange.Rows.Count).Row
IfLessOrEqual,LastCol,26, Return, (Chr(64+LastCol))
	Else IfGreater,LastCol,26, return, Chr((LastCol-1)/26+64) . Chr(mod((LastCol- 1),26)+65)

Return, PXL.Application.ActiveSheet.UsedRange.Columns(PXL.Application.ActiveSheet.UsedRange.Columns.Count).Column
;***********************First Column Alpha**********************************.
;~ XL_Last_Col_Alpha(XL)
IfLessOrEqual,LastCol,26, Return, (Chr(64+FirstCol))
	Else IfGreater,LastCol,26, return, Chr((FirstCol-1)/26+64) . Chr(mod((FirstCol- 1),26)+65)

XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean  Updated by jethrow*****************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
    If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
    Return	ComObjEnwrap(9,pacc,1)


Using MS Word to select email addresses and paste

select email

Select Email in MS Word

Microsoft Word has a very powerful search / replace feature (and a bit easier for most to play with than a Regular Expression).  One of the things I use it for is to select email addresses from a a document.

The following needs to be added to the search field: [A-Za-z0-9-._]{2,}[@]{1}[A-Za-z0-9-._][A-Za-z0-9-._][@]1[A-Za-z0-9-._]

Don’t forget to turn on Wildcards!

  • Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey