excel: how do i break up cell contents into CODE

Question

There is a code function that returns ASCII for every letter.

I would like to use it in a way that it will break up a cell a1 = "some string"

Into it's ASCII codes:

Something like this: "23423423434634"

Sorry I don't know the exact ASCII of that but you get my point.

please note that i would like to do this specifically with a formula and NOT with VBA

Answer 1

    string someText = "some string";
    CharEnumerator ce = someText.GetEnumerator();
    int counter = 0;
    while (ce.MoveNext())
    {
        char letter = someText[counter];
        //Call the function to get the ascii
        GetAsciiValue(letter);
        //Do something

        counter++;
    }

May be this might help you.

Regards, J'Sinh

Answer 2

Write an Excel User Defined Function.

Pseudocode for the function is below:

string returnValue;
for each ( char c in string)
   returnValue = returnValue + Chr(char)
return returnValue

You can call the UDF as part of an excell formula eg

=StringToASCIICodeValues(A1)

---

Formula Version

You could do it manually with excel builtin formula's by (excel doesn't have a for-loop function for formulae)

1. A1="some string"
2. A2="=MID($A$1,COLUMN(A2),1)"
3. Drag the formula in cell A2 to the right. Drag to K2 for some string example.
4. A3="=CODE(A2)"
5. Drag the formula in cell A3 to the right. Drag to K3 for some string example.
6. A4="=A3"
7. B4="=CONCATENATE(A4,B3)"
8. Drag the formula in cell B4 to the right. Drag to Cell K4 for some string example.
9. The right most column with a value on row 4 contains the final value. For some string it will return: 11511110910132115116114105110103 in cell K4

Answer 3

One way is to use a Byte Array to give Unicode number pairs for each character:

Sub ByteArray()
    Dim aByte() As Byte
    Dim str1 As String<
    Dim j As Long
    str1 = "ABC"
    aByte = str1
    For j = LBound(aByte) To UBound(aByte)
        MsgBox aByte(j)
    Next j
End Sub