Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
An example source cell would be something like:
Some:Thing:random:here
I want to transform it to this (reversing the order of each group):
here:random:Thing:Some
I would prefer a formula, without using macros, if possible.
This is the formula I've been trying to manipulate to change the last part of an IP.
=IF(C12="","",SUBSTITUTE(LEFT(SUBSTITUTE(C12,".","."&REPT("",LEN(C12))),LEN(C12)*2)&MID(SUBSTITUTE(C12,".","."&REPT("> ",LEN(C12))),LEN(C12)*2,LEN(C12))&"5"," ",""))
Assuming three colons as separators try this formula
=IF(C12="","",TRIM(MID(SUBSTITUTE(C12,":",REPT(" ",100)),300,100))&":"&TRIM(MID(SUBSTITUTE(C12,":",REPT(" ",100)),200,100))&":"&TRIM(MID(SUBSTITUTE(C12,":",REPT(" ",100)),100,100))&":"&LEFT(C12,FIND(":",C12)-1))
Explanation:
To get the part after the last colon I used this formula:
=TRIM(MID(SUBSTITUTE(C12,":",REPT(" ",100)),300,100))
That uses SUBSTITUTE to replace each colon with 100 spaces. As we are dealing with IP addresses each part is clearly a small number of characters so then using MID to take 100 characters starting at char 300 will include only the last part of the IP address and some spaces - TRIM then trims the spaces - I used the same approach to get the middle two parts and then LEFT function combined with FIND to get the first part
Assuming it is an IP address 1.2.3.4
dim astring as string
dim splat() as variant
astring = "1.2.3.4"
splat =split(string,".")
string=splat(3)&"."& splat(2)&"."& splat(1)&"."&splat(0)
So, there may be a way to solve this without using VBA, but here is a user-defined formula you can use to do exactly what you're asking for, as well as handle different delimiters:
Function ReverseList(InputString As String, Optional DelimiterString As String) As String
' Provide a default value of "," as the delimiter.
If Len(DelimiterString) = 0 Then
LabelString = ","
End If
' The InputArray() will contain the values, separated at the delimiter string.
Dim InputArray() As String
InputArray() = Split(InputString, DelimiterString)
' The OutputArray() will contain the values in reverse order.
Dim i As Integer
Dim j As Integer
j = UBound(InputArray)
Dim OutputArray() As String
ReDim OutputArray(0 To j) As String
For i = 0 To j
OutputArray(i) = InputArray(j - i)
Next i
' Now we put the reversed list back together, adding the delimiter back in.
ReverseList = Join(OutputArray(), DelimiterString)
End Function
You can call this in a cell with =ReverseList(A1, ":") in order to handle your example.
While it's scary to create your first UDF, it's pretty straightforward if you follow the steps.
