Complicated text rearrange

[phras81]

Hi I am hoping someone can help.

Looking for a formula to use on a large excel sheet to rearrange the characters in a specific order. The following is an example.

"1f6f33b9-35bc-6d4b-8377-ca5fc024eae1" to change to "b9336f1f-bc35-4b6d-8377-ca5fc024eae1"

Basically the first set of 8 characters and the second 2 sets of 4 characters need to be in reverse order in batches of 2.

Thank you in advance

 

[Joe4]

For an entry in cell A1, try this formula:

=MID(A1,7,2)&MID(A1,5,2)&MID(A1,3,2)&LEFT(A1,2)&"-"&MID(A1,12,2)&MID(A1,10,2)&MID(A1,14,LEN(A1))

 

[steve the fish]

Depends if you had textjoin function:

=TEXTJOIN(,,MID(A1,{7,5,3,1},2),"-",MID(A1,{12,10},2),"-",MID(A1,{17,15},2),MID(A1,19,LEN(A1)))

 

[phras81]

Hi Joe4

I have tried this which produces "b9336f1f-bc35-6d4b-8377-ca5fc024eae1". The second set of 4 characters "6d4b" also need reversing.

Can you help please?

Thank you

 

[Eric W]

If you have Excel 365 with the CONCAT function, this works:

=CONCAT(MID(A1,{7,5,3,1,9,12,10,14,17,15,19},{2,2,2,2,1,2,2,1,2,2,99}))

 

[phras81]

steve the fish thank you that has worked great

 

[phras81]

Thank you also Eric W

 

[Joe4]

Sorry, I missed that there were two sets of 4.

You already get better answers, but just to show you what that method I was using would look like, here it is:

=MID(A1,7,2)&MID(A1,5,2)&MID(A1,3,2)&LEFT(A1,2)&"-"&MID(A1,12,2)&MID(A1,10,2)&"-"&MID(A1,17,2)&MID(A1,15,2)&MID(A1,19,LEN(A1))

 

[Eric W]

Glad we could help.