Are there any built-in Excel functions that will reverse a string?
4 Answers
There is no built-in function that I know of, but you can create your own custom function.
First - create a new module:
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
Second - paste the following function in your new module (Reference):
Function Reverse(Text As String) As String
Dim i As Integer
Dim StrNew As String
Dim strOld As String
strOld = Trim(Text)
For i = 1 To Len(strOld)
StrNew = Mid(strOld, i, 1) & StrNew
Next i
Reverse = StrNew
End Function
Now you should be able to use the Reverse function in your spreadsheet
The current accepted answer is a poor way to reverse a string, especially when there is one built into VBA, use the following code instead (should act the same but run a LOT faster):
Function Reverse(str As String) As String
Reverse = StrReverse(Trim(str))
End Function
-
1@n00b - "how to create a module" wasn't the question. :) This solution is much more efficient/faster/easier, although I'm not sure why
TRIM
was added, since extra whitespace wasnt't specified in the question. For "huge" datasets, removeTRIM
if you don't need it. Oct 28, 2018 at 16:43
I found an alternative that doesn't require VBA at https://exceljet.net/formula/reverse-text-string
The formula is
=TEXTJOIN("",1,MID(B5,ABS(ROW(INDIRECT("1:"&LEN(B5)))-(LEN(B5)+1)),1))
where B5 is the input cell.
-
1
-
1
-
You can use this LET formula:
=LET( string, D6,
L, LEN( string ),
CONCAT( MID( string, SEQUENCE( 1, L, L, -1), 1) ) )
It breaks up the string into an array of 1-character cells and then recombines them back into a concatenated string in reverse order.