Write an Excel function to return ROT13'd text from A1 and put it into B1.
- User
Write an Excel function to return ROT13'd text from A1 and put it into B1.
Assistant
To apply the ROT13 cipher to the text in cell
A1and return the result inB1, you can use a VBA (Visual Basic for Applications) function. This is the most efficient and straightforward method in Excel, as the ROT13 algorithm involves character-by-character transformations that are not easily achievable using standard Excel formulas alone.
✅ Step-by-Step Instructions
-
Open the VBA Editor:
- Press
Alt + F11to open the VBA editor. - In the menu, go to Insert > Module to create a new module.
- Press
-
Paste the Following VBA Code:
Function ROT13(text As String) As String Dim i As Integer Dim char As String Dim result As String Dim code As Integer For i = 1 To Len(text) char = Mid(text, i, 1) code = Asc(char) If (char >= "A" And char <= "Z") Then ' Uppercase code = (code - 65 + 13) Mod 26 + 65 result = result & Chr(code) ElseIf (char >= "a" And char <= "z") Then ' Lowercase code = (code - 97 + 13) Mod 26 + 97 result = result & Chr(code) Else ' Non-alphabetic characters remain unchanged result = result & char End If Next i ROT13 = result End Function- Return to Excel and Use the Function:
- In cell
B1, enter the formula:=ROT13(A1)
- In cell
🧠 How It Works
- The
ROT13function iterates through each character in the input string. - It checks whether the character is uppercase or lowercase.
- It applies the ROT13 transformation using modular arithmetic to ensure proper wrapping around the alphabet (e.g.,
ZbecomesM). - Non-alphabetic characters (like spaces, numbers, or symbols) are left unchanged.
📌 Example
A1 B1 "Hello" "Uryyb" "World" "Jbeyq" "Excel!" "Rkcvq!"
📝 Notes
- This function works in all versions of Excel that support VBA (e.g., Excel 2007 and later).
- If you're using Excel 365 or Excel 2021, you could also achieve this using a formula-based approach with
TEXTJOIN,SEQUENCE, andMID. However, the VBA solution is more readable and efficient for this purpose.
✅ Final Answer
To return the ROT13'd text from
A1intoB1, use the following VBA function:Function ROT13(text As String) As String Dim i As Integer Dim char As String Dim result As String Dim code As Integer For i = 1 To Len(text) char = Mid(text, i, 1) code = Asc(char) If (char >= "A" And char <= "Z") Then code = (code - 65 + 13) Mod 26 + 65 ElseIf (char >= "a" And char <= "z") Then code = (code - 97 + 13) Mod 26 + 97 End If result = result & Chr(code) Next i ROT13 = result End FunctionThen, in cell
B1, enter:=ROT13(A1)-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.