Remove Trailing Zeros from a Hexadecimal string-CodePudding

I have a column of Hexadecimal strings with many TRAILING zeros. The problem i have is that the trailing Zeros from the string, needs to be removed

I have searched for a VBA formula such as Trim but my solution has not worked.

Is there a VBA formula I can use to remove all these Trailing zeros from each of the strings. An example of the HEX string is 4153523132633403277E7F0000000000000000000000000000. I would like to have it in a format of 4153523132633403277E7F

The big issue is that the Hexadecimal strings can be of various lengths.

CodePudding user response：

Formula:

You could try:

Formula in B1:

  =LET(a,TEXTSPLIT(A1,,"0"),TEXTJOIN("0",0,TAKE(a,XMATCH("?*",a,2,-1))))

This would TEXTSPLIT() the input and the fact that we can then use XMATCH() to return the position of the last non-empty string with a wildcard match ?*. However, given the fact we can use arrays in our TEXTSPLIT() function, a little less verbose could be:

=TEXTBEFORE(A1,TAKE(TEXTSPLIT(A1,TEXTSPLIT(A1,"0",,1)),,-1),-1)

Or another option, though more verbose, is to use REDUCE() for what it's intended to do, which is to loop a given array:

=REDUCE(A1,SEQUENCE(LEN(A1)),LAMBDA(a,b,IF(RIGHT(a)="0",LEFT(a,LEN(a)-1),a)))

VBA:

If VBA is a must, one way of dealing with this is through the RTrim() function. Since your HEX-string should not contain spaces to begin with I think the following is a safe bet:

Sub Test()

Dim s As String: s = "4153523132633403277E7F0000000000000000000000000000"
Dim s_new As String

s_new = Replace(RTrim(Replace(s, "0", " ")), " ", "0")

Debug.Print s_new

End Sub

If you happen to have spaces anywhere else in your string, another option would be to look for trailing zero's using a regular expression:

Sub Test()

Dim s As String: s = "4153523132633403277E7F0000000000000000000000000000"
Dim s_new As String

With CreateObject("vbscript.regexp")
    .Pattern = "0 $"
    s_new = .Replace(s, "")
End With

Debug.Print s_new

End Sub

Both the above options should print: 4153523132633403277E7F

CodePudding user response：

As far as I know, there is no function to do that for you. The way I would do it is presented in the pseudo-code below:

while last character is "0"
    remove last character
end while

It is quit slow, but VBA itself is not race car either, so you will probably not notice especially if you do not need to that for many times at once.

A more beautiful solution would involve VBA being able to search for the beginning or the end of a string.

An improvement of the solution above is to parse the string backwards and count the "0" characters, and then remove them all at the same time.