Conditionally remove a specific character from a string of text

[WeThotUWasAToad]

Hello,

Is it possible to duplicate the following Excel command in an AHK script?

=IF(MID(A1,LEN(A1)-1,1)=0,LEFT(A1,LEN(A1)-2)&RIGHT(A1,1),A1)

...which eliminates the 2nd-to-last character of a string of text if that character is a 0.

For example, suppose you have the following simple script in which you want to Copy a selected string of text, move down a row, and Paste the string of text there:

Code: Select all

F1::
Send ^c
Send {Down}
Send ^v
Return

However, suppose you have the condition that if the 2nd-to-last text character is a 0, then it must be eliminated in the pasted result.

Some examples:

[1] ABC0D → ABCD
[2] xy_z → xy_z
[3] xy_0z → xy_z
[4] A011011 → A011011
[5] b1010101 → b101011
[6] mn 0_ → mn _

Note that examples 2 & 4 do not have a 0 in the 2nd-to-last position and are therefore unchanged.

Thanks

[JoeWinograd]

Hi Steve,
I'm going to show you some simple AHK code that eliminates the next-to-last character of a string of text if that character is a zero. But before I do that, a high-level comment. I generally don't like the idea of sending keystrokes (or mouse clicks) with Excel (or Word). It is nearly always a much better approach to take advantage of the Component Object Model (COM) support in AHK. You and I hang out a lot at Experts Exchange and here's a thread there that you may find interesting:
https://www.experts-exchange.com/questi ... yntax.html

Note my comment in this post:
https://www.experts-exchange.com/questi ... #a41824810

The asker was sending mouse clicks and keystrokes, and while it's possible to get that working, the COM approach is the way to go, imo.

OK, with that out of the way, here's some simple, easy-to-understand code that eliminates the next-to-last character of a string if that character is a zero:

Code: Select all

If (Strlen(InputString)>1) ; leave InputString unchanged if it is not at least two chars
{
  StringRight,Last2Chars,InputString,2 ; get last two chars
  LastChar:=SubStr(Last2Chars,2,1) ; get last char
  NextToLastChar:=SubStr(Last2Chars,1,1) ; get next-to-last char
  If (NextToLastChar="0") ; check if next-to-last char is a zero
  {
    StringTrimRight,InputString,InputString,2 ; remove last two chars
    InputString:=InputString . LastChar ; add back the last char
  }
}

I put in comments and gave descriptive names to the variables, both of which I think make the code understandable, but let me know if there's anything that's not clear. There are no doubt faster ways to do this — a RegEx expert can probably do it in one impossible-to-understand line of code — but I prefer clarity over brevity in my code. Regards, Joe

[jeeswg]

Here are some possibilities:

Code: Select all

q::
;=IF(MID(A1,LEN(A1)-1,1)=0,LEFT(A1,LEN(A1)-2)&RIGHT(A1,1),A1)
vText := "abc0e"
;vText := "abcde"
if (SubStr(vText, StrLen(vText)-1, 1) = "0")
	vText2 := SubStr(vText, 1, -2) SubStr(vText, StrLen(vText))
else
	vText2 := vText
MsgBox, % vText2
return

;==================================================

w:: ;the same as above but using negative offsets
;and since negative offsets are handled differently by AHK v1/v2
;we check what version of AHK we are using via !!SubStr(1,0)
vText := "abc0e"
;vText := "abcde"
vIsV1 := !!SubStr(1,0)
if (SubStr(vText, vIsV1-2, 1) = "0")
	vText2 := SubStr(vText, 1, -2) SubStr(vText, vIsV1-1)
else
	vText2 := vText
MsgBox, % vText2
return

;==================================================

e::
vText := "abc0e"
MsgBox, % RegExReplace(vText, "0(?=.$)")
vText := "abcde"
MsgBox, % RegExReplace(vText, "0(?=.$)")
return

[WeThotUWasAToad]

Thanks for the responses & solutions.

Believe it or not, I've never used AHK String commands but after looking at several, I can see that I need to add them to my bag of tricks.

I generally don't like the idea of sending keystrokes (or mouse clicks) with Excel (or Word).

I suspect you are referring to VBA in Excel & Word because I don't know how to send clicks & keystrokes with Excel formulas. I use formulas all the time but if it comes to scripting something, I go to AHK rather than VBA

It is nearly always a much better approach to take advantage of the Component Object Model (COM) support in AHK.

I'm not familiar with COM. I just found what looks to be a basic tutorial here:
https://autohotkey.com/board/topic/6456 ... -webpages/

which I will put on my "to read" list unless you know of a better knowledge source.

[JoeWinograd]

> Believe it or not, I've never used AHK String commands but after looking at several, I can see that I need to add them to my bag of tricks.

Yes, hard to believe. I use string operations in just about all of my AHK scripts.

> suspect you are referring to VBA in Excel & Word

No. I was referring to the script in your initial post, which had Send ^c, Send {Down}, and Send ^v. I thought you were planning to use that for copy/paste with Excel, but perhaps that's not the case. I've worked with a lot of users who want to use AHK to navigate in an Excel spreadsheet (left, right, up, down) and copy/paste values (Ctrl-C/Ctrl-V). I try to steer them away from that and into using COM.

> I'm not familiar with COM.

You and I discussed this at Experts Exchange in this thread:
https://www.experts-exchange.com/questi ... cript.html

Note the COM-based code that I wrote for you in this post at that thread:
https://www.experts-exchange.com/questi ... #a41864006

> unless you know of a better knowledge source

Look at my recommendations for you in another post at that same EE thread:
https://www.experts-exchange.com/questi ... #a41864617
(especially kon's fantastic tutorial, MS Office COM Basics).

Here's another very interesting EE thread where you and I discussed COM:
https://www.experts-exchange.com/questi ... indow.html

Note, especially, this post at that thread:
https://www.experts-exchange.com/questi ... #a40638812

Regards, Joe

[jeeswg]

My strings/characters/RegEx tutorials might be useful.
jeeswg's homepage - AutoHotkey Community
https://autohotkey.com/boards/viewtopic ... 34#p144434

[WeThotUWasAToad]

My strings/characters/RegEx tutorials might be useful.

Sorry jeeswg. Didn't mean to be ignoring you. I saw your earlier post as well but I have yet to study it. And thanks for this tip too.

[Helgef]

Hello WeThotUWasAToad (and others)
You might find it useful to rewrite those excel functions in AHK, now, I know nothing about excel, so this is just a guess,

Code: Select all

A1:="ABC0D" ; → ABCD
A2:="xy_z" ;→ xy_z
A3:="xy_0z" ;→ xy_z
A4:="A011011" ;→ A011011
A5:="b1010101" ;→ b101011
A6:="mn 0_" ;→ mn _
loop 6
	msgbox % excelCmd(A%A_Index%)

excelCmd(string){
	if (MID(string,LEN(string)-1,1) = 0)
		return LEFT(string,LEN(string)-2) . RIGHT(string,1)
	return string
}

LEN(String){
	return strLen(String)
}	
MID(String, StartChar, Count){
	StringMid, out, String, StartChar, Count
	return out
}
LEFT(String, Count){
	StringLeft, out, String, Count
	return out
}
RIGHT(String, Count){
	StringRight, out, String, Count
	return out
}

I didn't comment the code, but if you follow JoeWinograd's nice example, you will get this too.
@ jeeswg's regex version
Cheers.

[WeThotUWasAToad]

> suspect you are referring to VBA in Excel & Word
No. I was referring to the script in your initial post, which had Send ^c, Send {Down}, and Send ^v. I thought you were planning to use that for copy/paste with Excel, but perhaps that's not the case.

OK, thanks for the clarification. I misunderstood this sentence from your earlier post:

I generally don't like the idea of sending keystrokes (or mouse clicks) with Excel (or Word).

...but now understand it to mean:
"I generally don't like the idea of [using AHK commands to] send keystrokes (or mouse clicks) [in] Excel (or Word)." Is that correct? If so then you've really got my curiosity piqued because those particular AHK commands are so simple (ie intuitive — because they so precisely duplicate the manual keystrokes). And yet you are enthusiastic about replacing them:

It is nearly always a much better approach to take advantage of the Component Object Model (COM) support in AHK.
[and]
I've worked with a lot of users who want to use AHK to navigate in an Excel spreadsheet (left, right, up, down) and copy/paste values (Ctrl-C/Ctrl-V). I try to steer them away from that and into using COM.

Also, regarding this:

Look at my recommendations for you in another post at that same EE thread:
https://www.experts-exchange.com/questi ... #a41864617
(especially kon's fantastic tutorial, MS Office COM Basics).

Here's another very interesting EE thread where you and I discussed COM:
https://www.experts-exchange.com/questi ... indow.html

Thanks a bunch for taking me back to those two EE threads Joe. One of them is from 2 1/2 years ago and the other from just under a year. (Incidentally, I think the latter is around the time you helped me realize that AHK can commonly — maybe always, I don't know — be used in place of VBA in MS Office apps.)

The main point though is that after twice failing to look further into this topic, I appreciate your giving me a 3rd opportunity.

[iseahound]

one line of RegEx

Code: Select all

newstring := RegExReplace(yourstring, "0?(.)$", "$1")

[WeThotUWasAToad]

one line of RegEx

Code: Select all

newstring := RegExReplace(yourstring, "0?(.)$", "$1")

That's pretty cool even though I don't understand it.

So is the best way to use it something like this?

Code: Select all

F1::
Send ^c
	Sleep, 100
Clipboard := RegExReplace(Clipboard, "0?(.)$", "$1")
Send {Down}
Send ^v
Return

This works also:

Code: Select all

F1::
Send ^c
	Sleep, 100
OutputVar := RegExReplace(Clipboard, "0?(.)$", "$1")
Send {Down}
Send %OutputVar%
Return

Both of the above scripts work great where I tried them (Notepad, Excel, Word) but the 2nd script consistently does something odd (in Excel only):

• After the OutputVar is inserted, two more {Down}'s occur. In other words, it behaves as though that script ends like this:

Code: Select all

...
Send {Down}
Send %OutputVar%
Send {Down 2}
Return

Again, that happens only with the second script and only in Excel. Any idea why?

Thanks

[iseahound]

Try adding small sleeps between sends. Also use ClipWaitinstead of Sleep, 100


From a million AHK script writers who have perfected the copy and paste functions

Code: Select all

   Copy() {
      AutoTrim Off
      c := ClipboardAll
      Clipboard := ""             ; Must start off blank for detection to work.
      Send, ^c
      ClipWait 0.5
      if ErrorLevel
         return
      t := Clipboard
      Clipboard := c
      VarSetCapacity(c, 0)
      return t
   }

   Paste(t) {
      Highlight.last := StrLenUnicode(t) - CountLines(t) + 1
      c := ClipboardAll
      Clipboard := t
      Send, ^v
      Sleep 50                    ; Don't change clipboard while it is pasted! (Sleep > 0)
      Clipboard := c
      VarSetCapacity(c, 0)        ; Free memory
      AutoTrim On
   }

[jeeswg]

When you use copy from Excel, it adds a trailing CRLF to the text, see what happens if you use the q subroutine below in Excel.

The w subroutine below, is based on your code above, but tries to account for the additional CRLF. If you don't remove that trailing CRLF before applying RegExReplace, then the script won't be removing the superfluous zeros.

Code: Select all

q::
SendInput, abcde`r`n
return

w::
Clipboard := ""
Send, ^c
ClipWait, 0.5
vText := Clipboard
if WinActive("ahk_class XLMAIN")
	vText := SubStr(vText, 1, -2)
vText := RegExReplace(vText, "0(?=.$)")
Clipboard := vText
SendInput, ^v
return

[JoeWinograd]

...but now understand it to mean:

"I generally don't like the idea of [using AHK commands to] send keystrokes (or mouse clicks) [in] Excel (or Word)." Is that correct?

Yes!

they so precisely duplicate the manual keystrokes

Yes, and that's both the good news and the bad news.

yet you are enthusiastic about replacing them

Indeed, I am! One problem with sending keystrokes is that they can get out of kilter and start going berserk. For example, I was trying to help a user who was sending mouse clicks and keystrokes with AHK. But during my testing, the focus window changed unexpectedly due to an external event, and all of a sudden mouse clicks and keystrokes — including the Delete key — were being sent to my file manager — scared the dickens out of me! I wound up completely rewriting the script with AHK COM calls.

Another problem is performance. Keystrokes are often sent too fast, and you'll frequently see the recommendation to put in Sleep commands to give the cursor time to land where it's supposed to be before sending the next keystroke. I've seen recommendations for sleep times as low as 50 milliseconds and up to several seconds. Those Sleep commands can add up to painful performance. In the script that I mentioned above, I was literally watching the cursor move down the A column to its end, then across to the top of the B column, then down the B column to its end, then across to the top of the C column, etc. Of course, there were Sleep commands all along the way — very painful to watch. I replaced all of that with a Loop command on the rows followed by a Loop command on the columns and COM calls inside the loop like this:

CurrentCell:=CurrentColumn . CurrentRow
ExcelCells[CurrentColumn,CurrentRow]:=oWorkbook.Worksheets(1).Range(CurrentCell).Value

Much more reliable and much faster!

AHK can commonly — maybe always, I don't know — be used in place of VBA in MS Office apps

Yes, and, in my experience, more reliably. For example, in another EE thread, the asker was trying to run a VBA macro that counted words in a DOC/DOCX file. It worked fine on a small doc, but on a large one (War and Peace — more than a half-million words), there was no progress (nothing in the status bar) after about five minutes. It seemed to be hanging, and then I got a VBA error dialog box that said "Run-time error '28': Out of stack space". I wrote an AHK script to do it, making Word COM calls, and it takes 72 seconds to process War and Peace — all 567,919 words — no problems, no errors. Of course, that's just one example, and it may have been due to a poorly written VBA macro. All I can say is that AHK's COM calls have always worked very well for me. Regards, Joe