Excel question

B

Brian

Is there a way that I can transpose the order of the values in a cell? For
example I have the values of 10.200.13.1 in a cell and I want to
transpose(not sure if that is the correct term or not) the order of the
values in the cell so that they appear as 1.13.200.10.

thanks for any help as I have literally 4 pages of these kind of values that
I have to flip.
 
M

mzehr

Hi
try the following user defined function from John
Walkenbach's book Excel 2000 formulas (great resource by
the way):

Option Explicit

Function REVERSETEXT(text) As String
' Returns its argument, reversed
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
REVERSETEXT = REVERSETEXT & Mid(text, i, 1)
Next i
End Function

HTH
 
M

mzehr

Brian,
I just realized that the UDF I sent you will totally
reverse all of the numbers, rather than just groups of
numbers. Back to the drawing board, I guess. Sorry.
 
J

Jason Morin

Not knowing what pattern the numbers follow (is it always
##.###.##.# ?), you can use this gargantuan formula:

=MID(A1,FIND("?",SUBSTITUTE(A1,".","?",LEN(A1)-LEN
(SUBSTITUTE(A1,".",""))))+1,999)&"."&MID(A1,FIND
(".",A1,FIND(".",A1)+1)+1,FIND("?",SUBSTITUTE
(A1,".","?",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-FIND
(".",A1,FIND(".",A1)+1)-1)&"."&MID(A1,FIND(".",A1)+1,FIND
(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)&"."&LEFT(A1,FIND
(".",A1)-1)

If it *is* always ##.###.##.#, then use:

=RIGHT(A1,1)&"."&MID(A1,8,2)&"."&MID(A1,4,3)&"."&LEFT
(A1,2)

HTH
Jason
Atlanta, GA
 
E

ElsiePOA

Are all of your data in the exact same format? (ie. xx.xxx.xx.x). I
so, I can write a fromula to accomplish what you want. If the numbe
of digits OR the numbers of decimal points varies from one to th
other, a formula will still do the job but it is a little mor
complicated
 
R

Ron Rosenfeld

Is there a way that I can transpose the order of the values in a cell? For
example I have the values of 10.200.13.1 in a cell and I want to
transpose(not sure if that is the correct term or not) the order of the
values in the cell so that they appear as 1.13.200.10.

thanks for any help as I have literally 4 pages of these kind of values that
I have to flip.
Click to expand...

You may try this VBA macro:

==========================
Option Explicit

Sub RevIP()
Dim c As Range
Dim i As Long, j As Long
Dim temp1 As Variant
Dim temp2()

For Each c In Selection
temp1 = Split(c.Text, ".")
ReDim temp2(UBound(temp1))
j = UBound(temp1)
i = 0
Do While i <= UBound(temp1)
temp2(j) = temp1(i)
i = i + 1
j = j - 1
Loop

c.Value = Join(temp2, ".")

Next c

End Sub
======================

Do a backup of your data before you do anything.

Assumes VBA6 (XL2000 or later) that has the Join and Split methods.

To enter this, <alt><F11> opens the VB Editor.

Ensure your project his highlighted in the Project Explorer window, then
Insert/Module and paste the above code into the window that opens.

Return to the worksheet.
Select your region that contains the strings to be reversed.
<alt><F8> opens the macro box. Select RevIP and run the macro.


--ron
 
B

Brian

Hello Jason. Here is a small sample of the ip addresses I have to work
with - so therefore they are not always the same lenght.
10.200.30.242
10.200.30.243
10.200.30.246
10.200.30.247
10.200.30.248
10.200.31.1
10.200.31.3
10.200.36.201
10.200.36.202
10.200.36.249
10.200.39.141
10.201.208.201
10.201.208.202

it appears your first example would work. Thanks.
 
B

Brian

Here is a sample of the format of the data

10.200.30.242
10.200.30.243
10.200.30.246
10.200.30.247
10.200.30.248
10.200.31.1
10.200.31.3
10.200.36.201
10.200.36.202
10.200.36.249
10.200.39.141
10.201.208.201
10.201.208.202
thanks for your help with this.
 
D

Dave Peterson

A non-formula, non-macro approach.

Insert 4 columns to the right of your column with data (is it just one column?)

Then do Data|text to columns and tell excel it's delimited by a dot.

Have the values go into the next 4 columns--don't use the original column.

Then concatenate those cells in a 5th column.

Say column A held the data, columns B:E held the separated values, put this in
F1 and drag down.

=e1&"."&d1&"."&c1&"."&b1

Convert to values (if you want) and delete the columns you don't need anymore.
 
Top