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

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 :)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Try this UDF

Option Explicit


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,



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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I would still keep the ModifierIndex as application.caller  restricts the user to enter the formula beyond the row 5 (in this case).

KevinAuthor Commented:
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.
The code is originally written by me, not Rgonzo.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.