We will be migrating from Ask to Discourse on the first week of August, read the details here

# reverse content in a Calc field

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.

edit retag close merge delete

Sort by » oldest newest most voted

Other option, array formula:

={CONCAT(LEFT(RIGHT(A1,ROW(INDIRECT("A1:A"&LEN(A1))))))}

Remember, you need accept with CTRL+SHIFT+ENTER

more

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'

( 2020-09-09 08:08:08 +0200 )edit

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?

[update] nevermind, found it inside the "Function Wizard" on the lower left corner the Array checkmark box... quite nicely hidden

( 2020-09-09 09:31:27 +0200 )edit

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 "," ...

( 2020-09-09 09:52:57 +0200 )edit

Bien@mauricio: I knew that someone was going to surpass my proposal. Upvoted!

( 2020-09-09 12:34:55 +0200 )edit

@mauricio: Great solution, thanks!

( 2021-02-13 19:32:04 +0200 )edit

hello @RDL854,

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,
'answer' only if you found a solution yourself ...

more

Hello,

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

Macro Function:

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!"

more