Need an Excel VBA fomula to modify string by adding number to each ASCII character.

Posted on 2014-08-05
Last Modified: 2014-08-05
I need some VBA code to modify strings based on using a character from a modifying word.  My source strings would be in column A and I would put the formula in each cell to the right of each string, in column B.

What I would like the formula to do is:
1.  Modify every ASCII character in the source string by
2. Using a "modifier" word inside the formula (let's say HELLO for now), change each character in the source word by converting it to a number (using CODE function), adding the CODE number of the letter in the "modifier" word that corresponds to the ROW of the source word.
3. Taking that new number and converting back to a character using CHAR.

For example if the word in Row 1 was "pasta", then I would use the code for "H", since "H" is the number 1 letter in my modifier word.  So...

p would convert to the number 112,
formula would add 72 to that (CODE FOR "H") to get 184
CHAR would convert 184 to ,

a would convert to the number 97,
formula would once again add 72 to that (CODE FOR "H") to get 169
CHAR would convert 169 to ©

and so forth, so that the formula that I place in Cell B1 would convert

pasta  (in cell A1) to


similarly (in this example) the word in cell A2 would be converted by using the letter "E" which is the second letter (cause it is in row 2)

so if the same word pasta were in row 2, (and I did it right) it would convert to


My modifier word will have 13 letters and my list of strings will go up to row 13.

This is not for a practical application, other than a fun project that I am working on.  Fairly urgent and many thanks if someone can put this together :)
Question by:Kevin
    LVL 18

    Assisted Solution


    Try this UDF

    Option Explicit
    Const MODIFIERSTRING    As String = "HELLO"
    Function MODIFIER(InputValue, ByVal ModifierIndex As Long) As String
        If TypeOf InputValue Is Range Then InputValue = InputValue.Value2
        Dim i   As Long, b() As Byte, m() As Byte, v As Long
        b = StrConv(InputValue, vbFromUnicode)
        m = StrConv(MODIFIERSTRING, vbFromUnicode)
        v = m(ModifierIndex - 1)
        For i = LBound(b) To UBound(b)
            MODIFIER = MODIFIER & Chr(b(i) + v)
    End Function

    Open in new window

    And try in B1 and copied down,


    LVL 47

    Accepted Solution


    with Application.Caller you do not need ModifierIndex

    Const MODIFIERSTRING As String = "Hello"
    Function MODIFIER(InputValue As String) As String
        Dim i As Long, b() As Byte, v As Long
        b = StrConv(InputValue, vbFromUnicode)
        v = Asc(Mid(MODIFIERSTRING, Application.Caller.Row, 1))
        For i = LBound(b) To UBound(b)
            MODIFIER = MODIFIER & Chr(b(i) + v)
    End Function

    Open in new window

    LVL 18

    Expert Comment

    I would still keep the ModifierIndex as application.caller  restricts the user to enter the formula beyond the row 5 (in this case).


    Author Closing Comment

    Thanks to both.  I preferred Rgonzo's solution, and also appreciate the fact that krishnkrkc added the ModifierIndex as a restriction (which is something new that I learned).

    I am very excited to "code" my list and tempt someone to decode it.  Should be fun.
    LVL 18

    Expert Comment

    The code is originally written by me, not Rgonzo.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now