How to copy vice versa? ..

[nedy_03]

Hi,

Is it posible to copy vice versa the content of a cell in another cell?? ...

Thx ,
Nedy

[CBrine]

nedy,
Can you expand your explanation a little? Are you trying to copy the contents of a cell to another cell manually? Are you using code? What do you mean by "vice versa", are you trying to transpose a range of cells?

Cal

[nedy_03]

By ex : In A1 I have let's say "12345". In B1 I need a formula that would return "54321" (the inverted content of A1) ...

Thx,
Nedy

[matthewspatrick]

Function TransformString(str As String, Optional DoTrim As Boolean = True, _
Optional Reverse As Boolean = True)
' Function transforms a string, either reversing it or randomizing it (Reverse = False)
' DoTrim indicates whether to trim leading and trailing blanks
' Function is non-volatile, so it will only change if an argument changed
Dim Counter As Long
Dim Letters As Collection
Dim TakeItem As Long

If DoTrim Then str = Trim(str)
If Len(str) = 0 Then
TransformString = ""
Exit Function
End If
If Reverse Then
For Counter = Len(str) To 1 Step -1
TransformString = TransformString & Mid(str, Counter, 1)
Next
Else
Set Letters = New Collection
For Counter = 1 To Len(str)
Letters.Add Mid(str, Counter, 1)
Next
For Counter = 1 To Len(str)
TakeItem = Int(Rnd * Letters.Count) + 1
TransformString = TransformString & Letters(TakeItem)
Letters.Remove TakeItem
Next
Set Letters = Nothing
End If

End Function

[nedy_03]

THX ...

[matthewspatrick]

You're welcome. Don't forget to mark the thread 'solved'.

:)

[Bob Phillips]

If it is a number, you can do it with this formula

=SUMPRODUCT((MID(A4,LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))+1,1))*10^(LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))))

[CBrine]

OK Bob,
You need to explain that one.:-)

I believe it's creating an array then * each value by 10 ^ ???.
So
12345
1->10^5
2->10^4
3->10^3
4->10^2
5->10^1

And the sum of this is the reverse of the original value.
The LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))
Is messing me up? How is taking a substring of original value, based on it's length -minus the row its in do this?
row(1:???) seems to always evalutate to 1? So couldn't you just replace the entire structure with a 1? What am I missing?

Cal

[Bob Phillips]

OK Bob,
You need to explain that one.:-)

I believe it's creating an array then * each value by 10 ^ ???.
So
12345
1->10^5
2->10^4
3->10^3
4->10^2
5->10^1

And the sum of this is the reverse of the original value.
The LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))
Is messing me up? How is taking a substring of original value, based on it's length -minus the row its in do this?
row(1:???) seems to always evalutate to 1? So couldn't you just replace the entire structure with a 1? What am I missing?

Cal
Okay, I'll try.

ROW(INDIRECT("1:"&LEN(A4)))

creates an array of 1,2,3,...,length of A4, so this takes care of the variable number

LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))+1

creates that array reversed, length of A4,...,3,2,1 - seeing as the guy wants it reversed, therefor

MID(A4,LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))+1,1)

takes each character fro A4 and returns an array of the characters reversed (78342 would be returned a {2,4,3,8,7}

10^(LEN(A4)-ROW(INDIRECT("1:"&LEN(A4))))

creates a similar sized array, of 10 to the power of the reversed element numbers (...,1000,100,10,1)

and finally these are multiplied.

Take an example

A4: 768

LEN(A4) gives 3

ROW(INDIRECT("1:"&LEN(A4)) gives {1,2,3}

LEN(A4)-ROW(INDIRECT("1:"&LEN(A4))) gives {2,1,0}

LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))+1 gives {3,2,1)

MID(A4,LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))+1,1) gives {8,6,7}

10^(LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))) gives {100,10,1}

Multiplying the two arrays using SP gives 8*100+6*10+7*1 = 867


Your comments have made me realise that I overdid it though. I was focussing on reversing the numbers, so I reversed the strings, but of course I could have just not bothered and multipled by the relevant powers of 10, like so

SUMPRODUCT((MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))*10^(ROW(INDIRECT("1:"&LEN(A5)))-1))

a bit simpler.

Does that make sense?

[johnske]

By ex : In A1 I have let's say "12345". In B1 I need a formula that would return "54321" (the inverted content of A1) ...

Thx,
NedyYou can use the StrReverse function for this, e.g.

Sub ReverseIt()
[B1] = StrReverse([A1])
End Sub

[CBrine]

Bob,
Thanks for the 'xplaining. What surprises me and confused me, was that the row() method was able to act upon this "virtual array" to return the correct value.

Cal

[Bob Phillips]

Cal,

That is because ROW returns an array, every time. If you put =ROW(A1) in a cell, it will show a 1, but if you evaluate the formula using F9, you will see ={1}, indicating that it is an array. Similalrly, =ROW(A1:A10) will show 1 again, but F9 shows {1;2;3;4;5;6;7;8;9;10}. This is a well proven technique to get an array of values, which in turn gives you the ability to do a 'batch' type operation within a formula, evaluate a number of items (be that a range or characters within a cell) one at a time. The INDIRECT is a way of passing variable data into the function.