CODEWITHSUNDEEP
excelasciiworksheet-function
Alex Gordon
Alex Gordon

Reputation: 60711

excel: how do i break up cell contents into CODE

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

Upvotes: 1

Views: 2296

Answers (4)

Saxon Druce
Saxon Druce

Reputation: 17624

Although it's ugly, if your strings are not too long you can do it with a long formula which converts each character and then joins them back together.

For example this formula can handle strings which are up to 20 characters in length (creating hex ascii codes):

=if(len(A1)>=1,DEC2HEX(CODE(MID(A1,1,1))),"")&
if(len(A1)>=2,DEC2HEX(CODE(MID(A1,2,1))),"")&
if(len(A1)>=3,DEC2HEX(CODE(MID(A1,3,1))),"")&
if(len(A1)>=4,DEC2HEX(CODE(MID(A1,4,1))),"")&
if(len(A1)>=5,DEC2HEX(CODE(MID(A1,5,1))),"")&
if(len(A1)>=6,DEC2HEX(CODE(MID(A1,6,1))),"")&
if(len(A1)>=7,DEC2HEX(CODE(MID(A1,7,1))),"")&
if(len(A1)>=8,DEC2HEX(CODE(MID(A1,8,1))),"")&
if(len(A1)>=9,DEC2HEX(CODE(MID(A1,9,1))),"")&
if(len(A1)>=10,DEC2HEX(CODE(MID(A1,10,1))),"")&
if(len(A1)>=11,DEC2HEX(CODE(MID(A1,11,1))),"")&
if(len(A1)>=12,DEC2HEX(CODE(MID(A1,12,1))),"")&
if(len(A1)>=13,DEC2HEX(CODE(MID(A1,13,1))),"")&
if(len(A1)>=14,DEC2HEX(CODE(MID(A1,14,1))),"")&
if(len(A1)>=15,DEC2HEX(CODE(MID(A1,15,1))),"")&
if(len(A1)>=16,DEC2HEX(CODE(MID(A1,16,1))),"")&
if(len(A1)>=17,DEC2HEX(CODE(MID(A1,17,1))),"")&
if(len(A1)>=18,DEC2HEX(CODE(MID(A1,18,1))),"")&
if(len(A1)>=19,DEC2HEX(CODE(MID(A1,19,1))),"")&
if(len(A1)>=20,DEC2HEX(CODE(MID(A1,20,1))),"")

For longer strings you could repeat this pattern, although you'd be limited by the maximum formula length allowed by Excel, which seems to be 8,192 characters.

Upvotes: 0

mikek3332002
mikek3332002

Reputation: 3562

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

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23505

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

Upvotes: 1

Jsinh
Jsinh

Reputation: 2579

    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

Upvotes: 0

Related Questions