Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
What have you tried so far? –  Tog Sep 26 '13 at 16:18
    
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

2 Answers 2

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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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