how to encrypt numbers in excel

[jatin78]

i want to encrypt numeric data ie (1 = a, 2 = b, 3 = c ......... 0 = j) now for number 123 i want return values as abc can anybody please help urgent

 

[Comfy]

Try this:

Excel 2010

	A	B	C
Original	Encrypted	Decrypted
abd	xxxQQX	abd
xxx
xxx
yjm5616-651yu	xxxIY_ KE	yjm5616-651yu

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]123[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]49687[/TD]

[TD="align: right"]49687[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=XorC(A2,123)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=XorC(B2,123)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




VBA Express : Excel - Encrypt and decrypt strings using a Xor algorithm

If you definitely want to return abc then we can put something together although due to it's simplicity it would seem pointless to encrypt it in the first place.

 

[jatin78]

Thanks for your reply Bro. but it is'nt what i need...,

say for eg i have BLACK WHITE as my encrypter where numeric values are from 1 to 0............ " B = 1, L = 2, A = 3 and so on till E = 0" now if i enter 750 in cell a2 i want to create a function which returns "HKE" max no. of digits will be 6 please help as need urgently...

Thanks in advance

 

[Comfy]

The additional info helps but your key is eleven characters long (10 letters and one space)

Also,

as need urgently...

Personally, when I see statements like this it makes me want to help less.

 

[Comfy]

Excel 2010
	A	B
2	750	HKE
Sheet2

Cell Formulas
Range	Formula
B2	=Enc(A2,"BLACKWHITE")

Function Enc(sData As String, sKey As String)
Dim temp As String
Dim i As Long
If Len(sKey) > 10 Then Enc = "Invalid Key Length"


For i = 1 To Len(sData)
    temp = temp & Mid(sKey, Replace(Mid(sData, i, 1), "0", "10"), 1)
Next i


Enc = temp
End Function

 

[Peter_SSs]

My slight variation.

I think that a short key length is more of a problem than a long one.
Also, Comfy, your "Invalid Key Length" never gets returned does it?

Function Encr(sData As String, sKey As String) As String
  Dim i As Long
  
  If Len(sKey) < 10 Then
    Encr = "Ivalid key length"
  Else
    sKey = Mid(sKey, 10, 1) & sKey
    For i = 1 To Len(sData)
      Encr = Encr & Mid(sKey, Mid(sData, i, 1) + 1, 1)
    Next i
  End If
End Function

Excel Workbook
	A	B
1	750	HKE
2	901256	TEBLKW
3	3265	Ivalid key length
Sheet2

 

[jatin78]

thanks a ton guys this worked, sorry comfy if disappointed you

 

[Peter_SSs]

thanks a ton guys this worked

You are welcome.

 

[edisun3003]

Sorry for re-opening this post after a long time.
I'm a very new to VB coding in excel sheets..
Can you please explain in detail how to implement this code.

I can see that u created a new formula named "ENC" using the VB code... but i need a step by step guidance

 

[Peter_SSs]

Can you please explain in detail how to implement this code.

Welcome to the MrExcel board!

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the function code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot in post 6. Hover over (or click) B1, B2 or B3 in that screen shot to see (or copy) the formula.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)