# separate,reverse,regroup

#### only_lonely

##### Board Regular
given a hex strings 57 31 7c 4d 30 30 30 30 30 31 35 36 33 7c 7c 54 45 53 54 4c
1. separate into 4 bytes per group
57 31 7c 4d
30 30 30 30
30 31 35 36
33 7c 7c 54
45 53 54 4c

2. reverse it from MSB to LSB way
57 31 7c 4d = 4d 7c 31 57
30 30 30 30 = 30 30 30 30
30 31 35 36 = 36 35 31 30
33 7c 7c 54 = 54 7c 7c 33
45 53 54 4c = 4c 54 53 45

3. regroup
4d 7c 31 57 30 30 30 30 36 35 31 30 54 7c 7c 33 4c 54 53 45

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Is your original string in one cell?

Are you looking for a sheet formula or vb?

Here's a UDF that might do the job for you:

Code:
`````` option base 0
Function separateReverseRegroup(ByVal strHex As String) As String
Dim spl As Variant
spl = Split(strHex, " ")
If UBound(spl) <> 19 Then
separateReverseRegroup = "Not Enough Elements"
Else
For i = 0 To 4
For j = 3 To 0 Step -1
separateReverseRegroup = separateReverseRegroup & spl(i * 4 + j) & " "
Next j
Next i
separateReverseRegroup = Trim(separateReverseRegroup)
End If
End Function``````
If you have your original string in A2 then

=separateReverseRegroup(A2)

will provide the transformed group.

Currently it's not very adaptable, for example, it only works for groups of 20.

HTH

SteO59L, yup...the codes is copied from application then i just paste it at one cell

thanks Weaver...it works...
but why only group of 20? i think my code will be very long

Will it at least always be in groups of 4?

I originally limited it so there'd be no problem trying to access elements of the array that don't exist. If you'd like me to mod it to work with variable lengths, let me know what the possible variations are and I'll see what I can do to change it.

yup..it must be 4 groups

Try this:

Code:
``````Option Base 0
Function separateReverseRegroup(ByVal strHex As String) As String
Dim spl As Variant, grpCount As Integer
spl = Split(Trim(strHex), " ")
grpCount = Int(UBound(spl) / 4) + 1
If (UBound(spl) + 1) Mod 4 <> 0 Then
separateReverseRegroup = "Not in groups of 4"
Else
For i = 0 To grpCount - 1
For j = 3 To 0 Step -1
separateReverseRegroup = separateReverseRegroup & spl(i * 4 + j) & " "
Next j
Next i
separateReverseRegroup = Trim(separateReverseRegroup)
End If
End Function``````

hooray...thanks alots....work lika a charm

can u teach me how u group, separate, then merge?
then i can do it myself when i need to form or reform any data...

To be fair, I don't know if you can break it out that way, but I'll try:
Code:
``spl = Split(Trim(strHex), " ")``
This breaks the string into an array (like a group of cells but 'internal'), by braking up the string based on the " " (space) character.
Code:
``grpCount = Int(UBound(spl) / 4) + 1``
Counts the elements in the array, bearing in mind the first one is referred to as spl(0), so you have to round up. If your groups get bigger, you'll need to change this bit.
Code:
``For i = 0 To grpCount - 1``
This loops through the number of loops
Code:
``For j = 3 To 0 Step -1``
goes through each group backwards, using this math
Code:
``spl(i * 4 + j)``
, so again you'd need to change the 3 and the 4 to suit your group length
Code:
``separateReverseRegroup = separateReverseRegroup & spl(i * 4 + j)``
Adds the individual chunks back into a string, one group at a time, starting with the last in the group and ending with the first in that group, including a space after each one
Code:
``separateReverseRegroup = Trim(separateReverseRegroup)``
removes the last space, as it isn't needed. It's easier to add it then remove it this way than to write an extra if statement to decide whether to add it or not.
Code:
``````Option Base 0
Function separateReverseRegroup(ByVal strHex As String) As String
Dim spl As Variant, grpCount As Integer, grpLen As Integer
grpLen = 4
spl = Split(Trim(strHex), " ")
grpCount = Int(UBound(spl) / grpLen) + 1
If (UBound(spl) + 1) Mod grpLen <> 0 Then
separateReverseRegroup = "Not in groups of " & grpLen
Else
For i = 0 To grpCount - 1
For j = grpLen - 1 To 0 Step -1
separateReverseRegroup = separateReverseRegroup & spl(i * grpLen + j) & " "
Next j
Next i
separateReverseRegroup = Trim(separateReverseRegroup)
End If
End Function``````
Added the variable grpLen to illustrate the previous points on group length

HTH

Replies
5
Views
136
Replies
24
Views
542
Replies
3
Views
255
Replies
5
Views
229
Replies
1
Views
86

1,207,260
Messages
6,077,352
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back