I have columns of math data, which I have converted to Hex in the next column. In a third column I need to reverse the order of the content of the Hex values. Example: 321 to 123.
Other option, array formula:
Remember, you need accept with CTRL+SHIFT+ENTER
i like! ‘kiss’ solutions (keep it simple and stupid),
even if they produce confusing masses of brackets,
beware that you have to put =CONCAT(LEFT(RIGHT(A1,ROW(INDIRECT(“A1:A”&LEN(A1)))))) in the cell,
the curly brackets are created with the acceptance as array formula with ‘ctrl-alt-enter’,
note that if you confirmed the formula wrong with ‘enter’ you need a ‘non-changing-edit’ to make it an array formula again with ‘ctrl-alt-enter’
I could not get this to work … i tried CTRL+SHIFT+ENTER and CTRL+ALT+ENTER but the formular did not reverse the string. Is there a way to set the array formula input via the menus?
nevermind, found it inside the “Function Wizard” on the lower left corner the Array checkmark box… quite nicely hidden
sorry, me bad, ctrl-shift-enter is right, it didn’t work for me in the first shot, i re-constructed from scratch (inner to outer formula) and in the end learned to leave out the curly brackets, add. problem is once wrong accepted it needs a non ‘changing edit’, and add. problem may be the separator sign, locale dependent you might need “;” instead of “,” …
Bien @mauricio: I knew that someone was going to surpass my proposal. Upvoted!
@mauricio: Great solution, thanks!
some googling around could have helped faster … just as i wanted to try and learn for myself:
put the following into a macro module:
option vbasupport 1 function test_strrev(cell) test_strrev = strreverse(cell.value) End Sub 'test_strrev(cell)
put e.g. the following in e.g. B1: “=test_strrev(A1)”,
observe “oguh” in B1 if A1 contains “hugo”,
observe “able was I ere I saw elba” in B1 if A1 contains “able was I ere I saw elba” not! being an error but a palindrome …
P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …
i think the following macro should do what you want. Just put it into Tools → Macros → “Edit Macros” and you can call it from inside any cell like this:
=STRREV(A1) =STRREV("Hello World") =STRREV(123)
The First argument kann be a cell reference (A1) or direct data (string,number,…), which will be interpreted as a string and reversed
Function STRREV(data as String) Dim out as String For i = 0 to Len(data) -1 out = out & Mid(data,Len(data) - i,1) Next STRREV=out End Function
Hope that helps.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
Have a nice day and let’s (continue to) “Be excellent to each other!”