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

  • 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"," ","")) Sep 26, 2013 at 16:47
  • The commas wont be in a set location would they? Wo;; there always be three columns?
    – PsychoData
    Sep 26, 2013 at 17:04
  • Why are you avoiding macros? This can be cleanly solved with a small User-Defined Function.
    – Dane
    Sep 26, 2013 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... Sep 26, 2013 at 20:37

3 Answers 3


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

  • This is kinda brilliant. It took me a bit to parse it. Any chance you'd explain what you're doing?
    – Dane
    Sep 26, 2013 at 21:25

Assuming it is an IP address

dim astring as string
dim splat() as variant

astring = ""
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.

  • 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! Sep 26, 2013 at 20:52
  • Creating a module is the secret step everyone needs to know about.
    – Dane
    Sep 26, 2013 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.