I am creating an excel file to swap excel columns which contains the number corresponding to the ASCII character such as low letter, upper letter, number, special characters.
Here is the original table and the corresponding letter to the number
A B C D E F G H
1 2 3 4 5 6 7 8
I want to swap each of the cell to the end. Meaning I need to swap 1 to 8. 2 to 7. 3 to 6.
A B C D E F G H
8 7 6 5 4 3 2 1
I would want to use the excel function to do this. Is there a way to achieve this? I have 156 columns.
what about this method.
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
If your data is always ordered then sorting would work.
But let's assume your data is not always alphabetically or numerically sortable, you can use a formula to reverse an unsorted list:
| - | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | A | B | C | D | E | F | G | H |
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 3 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 |
Rows 1 and 2 are your original data. Row 3 is the reversing formula.
Add the formula below into cell A3
=INDEX($A$2:$G$2, COLUMNS(A2:$G$2))
Note (important) that the first Range of the INDEX is absolute $A$2:$G$2, but the only the last value of the columns range is absolute A2:$G$2 (no dollars)
Either drag cell A3 across to H3 or copy cell A3 and paste over B3:H3
This has an advantage over plain sorting in that it can reverse unsorted lists for you.