vba excel - Find and replace on condition + within same cell multiple occurance

Question

I am trying to write a VBA code ; I have 3-days experience as vba programmer. So trying my best based on my pascal programming experience.

find number in hexadecimal string from excel, check the position of number if its odd then replace the number with new number. If its not odd then continue searching for other occurrence within the same string. I have 15,000 hexa strings where I need to recursively search. range(B1:B15000)

Example:

Hexa string - Cell B1  - 53706167686574746920616c6c9261676c696f2c206f6c696f20652070657065726f63696e692537

translates to text - Spaghetti all�aglio, olio e peperocini

i want to replace 92(�) with 65(e) but in hexa string you notice there are multiple occurrences of 92 number but only one 92 falls at odd position to be replaced.

In excel I tried following:

=IF(ISODD(IF(ISERROR(SEARCH(92,B5)),0,SEARCH(92,B5)))=TRUE,SUBSTITUTE(B5,92,"27"),"no 92")

This works only for first occurrence in cell, tried modifying it to search further but no luck:

=IF(ISODD(IF(ISERROR(SEARCH(92,B6)),0,SEARCH(92,B6)))=TRUE,SUBSTITUTE(B6,92,"27"),IF(ISODD(IF(ISERROR(SEARCH(92,B6,SEARCH(92,B6)+1)),0,SEARCH(92,B6,SEARCH(92,B6)+1)))=TRUE,SUBSTITUTE(B6,92,"27"),"no 92"))

Any suggestions are welcome.

FunThomas · Accepted Answer · 2017-09-26 07:43:57Z

1

How about a small UDF, looking only at every second position?

Function replaceWhenAtOddPos(ByVal s As String, findStr As String, replaceStr As String)

    replaceWhenAtOddPos = s
    If Len(findStr) <> 2 Or Len(replaceStr) <> 2 Then Exit Function

    Dim i As Long
    For i = 1 To Len(s) Step 2
        If Mid(s, i, 2) = findStr Then s = Left(s, i - 1) & replaceStr & Mid(s, i + 2)
    Next i
    replaceWhenAtOddPos = s
End function

call:

replaceWhenAtOddPos("53706167686574746920616c6c9261676c696f2c206f6c696f20652070657065726f63696e692537", "92", "65")

answered Sep 26, 2017 at 7:43

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Saga Over a year ago

Hi Thomas, Thanks a lot for quick reply. This solves the issue .

Jochen · Accepted Answer · 2017-09-26 07:53:50Z

0

Please put the following UDF in a standard module:

Public Function replace_hex(str As String, srch As Integer, repl As Integer) As String
Dim pos As Integer
    pos = InStr(pos + 1, str, CStr(srch))
    Do Until pos = 0
        If pos Mod 2 = 0 Then str = Left(str, pos - 1) & CStr(repl) & Mid(str, pos + 2)
        pos = InStr(pos + 1, str, CStr(srch))
    Loop
replace_hex = str
End Function

and call it in your worksheet like that: =replace_hex(A1,92,65)

answered Sep 26, 2017 at 7:53

Jochen

1,2541 gold badge7 silver badges9 bronze badges

1 Comment

Saga Over a year ago

Hi Jochen, Thanks lot for quick reply and suggestions. Also tested your solution but it seems it does not change the odd position character. Example : 5461726c79206d65742070616b736f6920656e2067616d62619273 string after I set the =replace_hex(B1,92,27) - 5461726c72706d65742070616b736f6270656e2067616d62619273 So basically first 92 from the search string is replaced but the last 92 on odd position is ignored.

Collectives™ on Stack Overflow

vba excel - Find and replace on condition + within same cell multiple occurance

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related