Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

An example source cell would be something like:


I want to transform it to this (reversing the order of each group):


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"," ",""))

share|improve this question
I've been trying to manipulate a formula that I use 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"," ","")) – BondUniverse Sep 26 '13 at 16:47
The commas wont be in a set location would they? Wo;; there always be three columns? – PsychoData Sep 26 '13 at 17:04
Why are you avoiding macros? This can be cleanly solved with a small User-Defined Function. – Dane Sep 26 '13 at 17:15
There will prob. always be 3 ".", so 4 parts of data (although the last part could have extra stuff after it). In a perfect world, I would like it to be dynamic, but I'd be more than happy with the 4 parts (3 separators). The length of each "part" will vary some... – BondUniverse Sep 26 '13 at 20:37
up vote 3 down vote accepted

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))


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

share|improve this answer
This works perfectly! Thank you! – BondUniverse Sep 26 '13 at 20:50
This is kinda brilliant. It took me a bit to parse it. Any chance you'd explain what you're doing? – Dane Sep 26 '13 at 21:25
Thanks Dane, I added an explanation...... – barry houdini Sep 26 '13 at 22:03

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.

share|improve this answer
Dealing with the scripting in newer versions of Excel require the different file extension as well as permission settings. If this were just for me, I might be more apt to go this route. I REALLY appreciate your link though as I've tried using VBA, but couldn't get them to work (I forgot to create a module), so thank you soooo much for that link! Definitely keeping your script in mind in case I need it for something more complex! – BondUniverse Sep 26 '13 at 20:52
Creating a module is the secret step everyone needs to know about. – Dane Sep 26 '13 at 21:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .