PC Review


Reply
Thread Tools Rate Thread

Coverting Alphabets into Numbers

 
 
=?Utf-8?B?TWFyY3Vz?=
Guest
Posts: n/a
 
      24th May 2007
I hope someone can help me with this.

I have a column of "encrypted" data (in excel), which needs to be converted.
The data in each cell is "encrypted" by following this simple rule:

A = 1
B = 2
C = 3
D = 4
E = 5
F = 6
G = 7
H = 8
I = 9
J =0

Therefore, if a cell has "BFDJ", the numerical value will be 2640.

My problem is this:

How can I take the sum of the "encrypted" data in the column to make a grand
total, which is also encrypted using the above rule?

I've looked around and it seems that there are functions which can change
numbers into words, but not the other way round.

Any assistance is much appreciated.
 
Reply With Quote
 
 
 
 
Dave Miller
Guest
Posts: n/a
 
      24th May 2007
-Paste this code into a module
-Type, "BFDJ", into cell "A1"
-Type, =Decode("A1"), in cell "A2"
-Cell "A2" will return 2640

Regards,
Dave


Public Function DeCode(r As Variant) As Long
Dim i As Integer, _
l As String

For i = 1 To Len(r)
l = l & GetValue(Mid(r, i, 1))
Next

DeCode = l
End Function

Private Function GetValue(s As String) As String
Select Case s
Case Is < "J"
GetValue = Asc(s) - 64
Case "J"
GetValue = "0"
End Select
End Function

 
Reply With Quote
 
=?Utf-8?B?Sm9lbA==?=
Guest
Posts: n/a
 
      24th May 2007
Try this function. It will give you the decoded total for any range of cells
you choose.

=decodenum(A1:B5)

Function decodenum(Target As Range) As Integer

Total = 0

For Each cell In Target


Subtotal = 0
For Count = 1 To Len(cell)
Subtotal = (10 * Subtotal) + _
((Asc(Mid(cell, Count, 1)) - Asc("A") + 1) Mod 10)

Next Count

Total = Total + Subtotal
Next cell
decodenum = Total
End Function


"Marcus" wrote:

> I hope someone can help me with this.
>
> I have a column of "encrypted" data (in excel), which needs to be converted.
> The data in each cell is "encrypted" by following this simple rule:
>
> A = 1
> B = 2
> C = 3
> D = 4
> E = 5
> F = 6
> G = 7
> H = 8
> I = 9
> J =0
>
> Therefore, if a cell has "BFDJ", the numerical value will be 2640.
>
> My problem is this:
>
> How can I take the sum of the "encrypted" data in the column to make a grand
> total, which is also encrypted using the above rule?
>
> I've looked around and it seems that there are functions which can change
> numbers into words, but not the other way round.
>
> Any assistance is much appreciated.

 
Reply With Quote
 
NickHK
Guest
Posts: n/a
 
      25th May 2007
Marcus,
Not sure if this wil prove faster, but avoiding the Mid/Left etc text
function in favour of maths (and 1 concatenation) is an alternative:

Public Function DecodeString(EncodedStr As String) As Long
Dim bytArray() As Byte
Dim i As Long
Dim OutStr As String

Const ASCII_OFFSET As Long = 64
Const MAX_CODE As Long = 10

bytArray = UCase(EncodedStr)

For i = LBound(bytArray) To UBound(bytArray) Step 2
bytArray(i) = bytArray(i) - ASCII_OFFSET
If bytArray(i) >= MAX_CODE Then bytArray(i) = 0
OutStr = OutStr & bytArray(i)
Next

DecodeString = CLng(OutStr)

End Function

NickHK

"Marcus" <(E-Mail Removed)> wrote in message
news:6562C410-4DA4-4FE2-96F9-(E-Mail Removed)...
> I hope someone can help me with this.
>
> I have a column of "encrypted" data (in excel), which needs to be

converted.
> The data in each cell is "encrypted" by following this simple rule:
>
> A = 1
> B = 2
> C = 3
> D = 4
> E = 5
> F = 6
> G = 7
> H = 8
> I = 9
> J =0
>
> Therefore, if a cell has "BFDJ", the numerical value will be 2640.
>
> My problem is this:
>
> How can I take the sum of the "encrypted" data in the column to make a

grand
> total, which is also encrypted using the above rule?
>
> I've looked around and it seems that there are functions which can change
> numbers into words, but not the other way round.
>
> Any assistance is much appreciated.



 
Reply With Quote
 
=?Utf-8?B?TWFyY3Vz?=
Guest
Posts: n/a
 
      28th May 2007
Thank you all. From the examples, I was also able to figure out how to
"encrypt" it back to alphabets.

Thanks!

"Joel" wrote:

> Try this function. It will give you the decoded total for any range of cells
> you choose.
>
> =decodenum(A1:B5)
>
> Function decodenum(Target As Range) As Integer
>
> Total = 0
>
> For Each cell In Target
>
>
> Subtotal = 0
> For Count = 1 To Len(cell)
> Subtotal = (10 * Subtotal) + _
> ((Asc(Mid(cell, Count, 1)) - Asc("A") + 1) Mod 10)
>
> Next Count
>
> Total = Total + Subtotal
> Next cell
> decodenum = Total
> End Function
>
>
> "Marcus" wrote:
>
> > I hope someone can help me with this.
> >
> > I have a column of "encrypted" data (in excel), which needs to be converted.
> > The data in each cell is "encrypted" by following this simple rule:
> >
> > A = 1
> > B = 2
> > C = 3
> > D = 4
> > E = 5
> > F = 6
> > G = 7
> > H = 8
> > I = 9
> > J =0
> >
> > Therefore, if a cell has "BFDJ", the numerical value will be 2640.
> >
> > My problem is this:
> >
> > How can I take the sum of the "encrypted" data in the column to make a grand
> > total, which is also encrypted using the above rule?
> >
> > I've looked around and it seems that there are functions which can change
> > numbers into words, but not the other way round.
> >
> > Any assistance is much appreciated.

 
Reply With Quote
 
Bernd
Guest
Posts: n/a
 
      29th May 2007
Hello Marcus,

Just for the fun of it: If you like to decrypt via worksheet
functions, I suggest to use
=SUMPRODUCT(MOD(CODE(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),1))-64,10),
10^(LEN($A$1)-ROW(INDIRECT("1:"&LEN($A$1)))))

Regards,
Bernd

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separate Text into numbers and alphabets Sheeloo Microsoft Excel Worksheet Functions 4 10th Sep 2008 11:49 AM
Numbers to Alphabets rk0909 Microsoft Excel Misc 10 12th Jan 2008 11:38 AM
REMOVE THE ALPHABETS AND NUMBERS FROM WORKSHEET COZETTA JOHNSON Microsoft Excel Discussion 1 2nd Nov 2007 08:52 AM
how to change the alphabets to numbers and numbers to alphabets in =?Utf-8?B?SWduZXNod2FyYSByZWRkeQ==?= Microsoft Excel Worksheet Functions 2 20th Sep 2007 04:56 PM
columns showing numbers instead of alphabets =?Utf-8?B?aW5lbmV3Ymw=?= Microsoft Excel Misc 1 26th Jan 2007 12:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:46 AM.