CODEWITHSUNDEEP
excelexcel-formula
ZgrKARALAR
ZgrKARALAR

Reputation: 489

How to reverse text in EXCEL without VBA

I want to reverse long text in excel using without using VBA.

My text like this,

0000 12341252 LONGTEST 39192329 3 ABECIEEE 8899123 44 CCCCCCC 20%

I want to reverse this text like this,

%02 CCCCCCC 44 3219988 EEEICEBA 3 92329193 TSETGNOL 25214321 0000

After all research i found something and try to reverse with this formula

=TRIM(CONCAT(IFERROR(MID(A3;(LEN(A3)+1)-COLUMN($A:$AK);1);"")))

Result is

%02 CCCCCCC 44 3219988 EEEICEBA 3 923

In my case problem is this I cannot add anything in the VBA part when I research i found some function which I can add as a module in excel and use it but I need to make this with formula.

Upvotes: 1

Views: 3974

Answers (3)

JvdV
JvdV

Reputation: 75950

I know, this is an old question but thought I'd share two more options:

Formula:

I suppose this is looking after @Scott_Craner's answer but using SEQUENCE():

=CONCAT(MID(A1,LEN(A1)-SEQUENCE(LEN(A1))+1,1))

Or, for a range of cells:

=MAP(A1:A9,LAMBDA(s,CONCAT(MID(s,LEN(s)-SEQUENCE(LEN(s))+1,1))))

Python:

With the new PY() function you could also use:

xl("A1")[::-1]

Or, for a range of cells:

[s[::-1] for s in xl("A1:A9")[0]]

Upvotes: 1

urdearboy
urdearboy

Reputation: 14590

{=TEXTJOIN("",1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))-(LEN(B5)+1)),1))}

Array formula which must be confirmed with CTRL+SHIFT+ENTER

enter image description here

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152605

The issue is that COLUMN($A:$AK) is limiting it to a fixed number of characters.

Use:

 =CONCAT(MID(A1,LEN(A1)-ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)))+1,1))

this is an array formula and must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

I use INDEX over INDIRECT as INDIRECT is Volatile and will re-calc unnecessarily.

It is now dynamic and will take any size string.

enter image description here

Upvotes: 4

Related Questions