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

Ask Your Question

reverse content in a Calc field

asked 2020-09-08 20:51:12 +0200

RDL854 gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2020-09-09 05:10:32 +0200

Other option, array formula:


Remember, you need accept with CTRL+SHIFT+ENTER

image description

edit flag offensive delete link 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'

newbie-02 gravatar imagenewbie-02 ( 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

igorlius gravatar imageigorlius ( 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 "," ...

newbie-02 gravatar imagenewbie-02 ( 2020-09-09 09:52:57 +0200 )edit

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

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

@mauricio: Great solution, thanks!

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

answered 2020-09-09 00:26:31 +0200

newbie-02 gravatar image

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

edit flag offensive delete link more

answered 2020-09-09 00:52:06 +0200

igorlius gravatar image

updated 2020-09-09 00:57:06 +0200


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("Hello World")

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2020-09-08 20:51:12 +0200

Seen: 255 times

Last updated: Sep 09 '20