Поделиться через

Reverse string in Excel using array formula and without VBA

  • Статья

Being in the middle of something, suddenly the need to reverse a string in Excel arose.
Using the reversed string, it is easy to get the position of a comma before another string.
This time, I wanted to reverse the string without using VBA and its StrReverse-Function.

How it looks like: array formula

Reverse content of cell A2 with array formula (<ctrl>+<shift>+<enter> instead of <enter> after entering the formula).

German Syntax:

 =WENN(A2="";"";TEXTKETTE(TEIL(A2;1 + LÄNGE(A2)-ZEILE(INDIREKT("1:" & LÄNGE(A2)));1)))

English Syntax:

 =IF(A2="";"";CONCAT(MID(A2;1 + LEN(A2)-ROW(INDIRECT("1:" & LEN(A2)));1)))

How it works

The formula concatenates all characters of the string, starting at the last one till the first one.

Basic Structure

 =IF(A2="";"";<Do the work...>)

If the string is empty, no need to reverse something.

Do the actual work

Details of the non-empty part:

 CONCAT(
 MID(A2;1 + LEN(A2) - ROW(INDIRECT("1:" & LEN(A2))); 1)
 )

Steps:

  1. Get length of string
  2. Create counter using a range "1:x", whereas "x" contains the length of the string
  3. Get each single character, starting at the last one at position
    • character at LEN(A2), which is the last character: MID(A2; 1 + [LEN(A2)] - 1; 1) => MID(A2; [LEN(A2)]; 1)
    • character at LEN(A2) - 1: MID(A2; 1 + [LEN(A2)] - 2; 1)
    • ...
    • first character of A2: MID(A2; 1 + [LEN(A2)] - [LEN(A2)]; 1) => MID(A2; 1, 1)
  4. Concatenate the characters again

Further Reading