Swapping two strings inside an excel cell with each other based on orientation within the cell

Question

I'm trying to figure out how to swap certain characters with each other within a cell based on their orientation within the cell and not the actual characters themselves. This is because the strings are random. So for example, I would like to switch the 7th,8th, and 9th character with the 11th,12th, and 13th character.

How can I replicate this in a formula?

50000-ABC-123
50000-DEF-546
50000-GFD-456
50000-EDG-875
50000-SDF-304

50000-123-ABC
50000-546-DEF
50000-456-GFD
50000-875-EDG
50000-304-SDF

EDS · Accepted Answer · 2021-08-12T13:58:48.007

3

Another way is to use FILTERXML if you have the newest version of Excel, which will work even if the strings come in different lengths, but follow the same format (i.e. numbers-letters-numbers).

For example:

=TEXTJOIN("-",1,
INDEX(FILTERXML("<t><s>"&SUBSTITUTE(I2,"-","</s><s>")&"</s></t>","//s"),{1,3,2}))

Thanks to @JvdV for both the edit recomendation and the very encompassing explanation on FILTERXML here

edited Aug 12 '21 at 13:58

answered Aug 12 '21 at 13:30

EDS

2,155
1
6
21

2

Nice, did not think to use ```INDEX```. Have edited answer – EDS Aug 12 '21 at 13:59

score 2 · Answer 2 · answered Aug 12 '21 at 13:24

2

Just use this formula in B1 and pull it down

=LEFT(A1,5) & RIGHT(A1,4) & MID(A1,6,4)

answered Aug 12 '21 at 13:24

Pᴇʜ

56,719
10
49
73

1

This is exactly what I was looking for. Thank you! – Matt Aug 12 '21 at 13:26

Swapping two strings inside an excel cell with each other based on orientation within the cell

2 Answers2