Parsing a string and replacing characters using VLookUp

[BrandynBlaze]

Hello,

I'm just trying to build a spreadsheet that will allow me to paste a column of text strings into column C that will parse the text and replace any non-alphanumeric characters with their octal codes (For example turn "(TM)" into "\236"). The symbols to replace will be in column A, the codes to replace it with will be in column B, and I want column D to return the String with the appropriate substitutions made. I'm pretty comfortable with Vlookup but not with parsing a string in excel so hopefully someone can help!

Thanks,

Brandynblaze

 

[Rick Rothstein]

I'm just trying to build a spreadsheet that will allow me to paste a column of text strings into column C that will parse the text and replace any non-alphanumeric characters with their octal codes (For example turn "(TM)" into "\236"). The symbols to replace will be in column A, the codes to replace it with will be in column B, and I want column D to return the String with the appropriate substitutions made. I'm pretty comfortable with Vlookup but not with parsing a string in excel so hopefully someone can help!

First off, I am pretty sure the Octal code for the Trademark symbol is 231, not 236.

Can you clarify a couple of things for us? Do you have the actual trademark symbol as part of your text or do you have the four characters "(TM)" in the text and you want them recognized as the trademark symbol? Also, the two sections I highlighted in red seem to be contradictory... do you want any non-alphanumeric characters (including the common ones... periods, commas, dashes, etc.) converted as the first section says or only some subset of symbols that you listed in Column A tat the section section implies?

 

[hippiehacker]

Hello,

I'm just trying to build a spreadsheet that will allow me to paste a column of text strings into column C that will parse the text and replace any non-alphanumeric characters with their octal codes (For example turn "(TM)" into "\236"). The symbols to replace will be in column A, the codes to replace it with will be in column B, and I want column D to return the String with the appropriate substitutions made. I'm pretty comfortable with Vlookup but not with parsing a string in excel so hopefully someone can help!

Thanks,

Brandynblaze

a nice one here are some functions which will extract the non-numeric characters and convert to octal

Function octa(strTest As String)
    Dim lMyNewString As String
         
    lMyNewString = StripNumber(strTest)
    j = 0
   For i = 1 To Len(lMyNewString)
   j = j + DecimalToOctal2(Asc(Mid(lMyNewString, i, 1)))
   Next
   octa = j
End Function
 
Private Function StripNumber(stdText As String)
    Dim str As String, i As Integer
     'strips the number from a longer text string
    stdText = Trim(stdText)
     
    For i = 1 To Len(stdText)
        If Not IsNumeric(Mid(stdText, i, 1)) Then
            str = str & Mid(stdText, i, 1)
        End If
    Next i
     
    StripNumber = str ' * 1
     
End Function


Private Function DecimalToOctal2(intNumber As Integer)
Dim intRemainder As Integer
Dim strRemainder As String
Dim strOctalNumber As String

Do While intNumber >= 1
    intRemainder = intNumber Mod 8
    strRemainder = CStr(intRemainder)
    intNumber = intNumber \ 8
    strOctalNumber = strRemainder & strOctalNumber
Loop
DecimalToOctal2 = strOctalNumber
End Function

might need some re-thinking because I add the numbers together if you have more than one non-numeric character but might get you started as well

 

[BrandynBlaze]

First off, I am pretty sure the Octal code for the Trademark symbol is 231, not 236.

Can you clarify a couple of things for us? Do you have the actual trademark symbol as part of your text or do you have the four characters "(TM)" in the text and you want them recognized as the trademark symbol? Also, the two sections I highlighted in red seem to be contradictory... do you want any non-alphanumeric characters (including the common ones... periods, commas, dashes, etc.) converted as the first section says or only some subset of symbols that you listed in Column A tat the section section implies?

Okay, so I'm not sure why the standard octal code is different than what we use but \236 is our code for a TM for whatever reason... Basically what this is for is converting text to a format that our printing machinery can use, and we need to use our codes for non-standard symbols, but we only have a problem with a small subset. I don't want an automatic conversion because converting all text would leave it too messy and I only want a subset of the codes replaced. I may use both forms of the trademark symbol for replacement as both of them appear in our customer created spreadsheets. The primary symbols of concern are Spanish language accents, quotations and apostrophes, registered trademarks, trademarks, copyrights, and fractions... I have the codes so the particulars of that shouldn't matter as much, I just need to be able to use Vlookup like normal only parsing the string instead of taking the entire contents into account. Hopefully this clarifies things a bit, or at least produces fewer questions than answers