how can I reverse the characters of cells.

(data) (should be)
12345 54321
2456 6542
asdf fdsa
23 32

any formula which can do this?


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551348

Can't think of a generic formula but you could use VBA. Position cursor
over the cell you want to reverse and then run RunReverseData

Sub RunReverseData()
Dim CellAddress as string

CellAddress = Activecell.Address
Call ReverseData(CellAddress)
End Sub

Sub ReverseData(byval CellRef as string)
Dim i as Integer
Dim Data as string
Dim ShouldBe as string

ShouldBe=""
Data = Range(CellRef).Value
for i = 1 to Len(Data)
ShouldBe=ShouldBe & Mid(Data,Len(Data) - (i-1),1)
Next i
Range(CellRef).Value = ShouldBe
End Sub

thanks but I need formula. any guru... on this problem


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551348

starguy wrote:
how can I reverse the characters of cells.

(data) (should be)
12345 54321
2456 6542
asdf fdsa
23 32

any formula which can do this?

If you know the maximum size of the string in a cell (I shall assume 16)
then:

=MID(A1,16,1)&MID(A1,15,1)&MID(A1,14,1)&MID(A1,13, 1)&MID(A1,12,1)&MID(A1,11,1)&MID(A1,10,1)&MID(A1,9 ,1)&MID(A1,8,1)&MID(A1,7,1)&MID(A1,6,1)&MID(A1,5,1 )&MID(A1,4,1)&MID(A1,3,1)&MID(A1,2,1)&MID(A1,1,1 )

starguy wrote:
how can I reverse the characters of cells.

(data) (should be)
12345 54321
2456 6542
asdf fdsa
23 32

any formula which can do this?

alternatively, change the reversedata sub into a function:

Function ReverseData(cellcont)
Dim i As Integer
Dim ShouldBe As String

ShouldBe = ""
For i = 1 To Len(cellcont)
ShouldBe = ShouldBe & Mid(cellcont, Len(cellcont) - (i - 1), 1)
Next i
ReverseData = ShouldBe
End Function

thanks its good and easy but too much lengthy to type especially when I
have to use it frequently but with different data.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551348

starguy wrote:
thanks its good and easy but too much lengthy to type especially when
I have to use it frequently but with different data.
Confused???
What is too lengthy to type?

this formula of MID...

Paul Lautman Wrote:
starguy wrote:
thanks its good and easy but too much lengthy to type especially
when
I have to use it frequently but with different data.
Confused???
What is too lengthy to type?


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551348

this formula of MID...

Paul Lautman Wrote:
starguy wrote:
thanks its good and easy but too much lengthy to type especially
when
I have to use it frequently but with different data.
Confused???
What is too lengthy to type?


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551348

starguy wrote:
this formula of MID...

Paul Lautman Wrote:
starguy wrote:
thanks its good and easy but too much lengthy to type especially
when I have to use it frequently but with different data.
Confused???
What is too lengthy to type?

Well, first of all you posted the statment attached to my post about the VBA
function not the MID one. With the VBA function you only need to type
something like =ReverseData(A1) in a cell or possibly
=PERSONAL.XLS!ReverseData(A1) depending on where you stored the UDF.

Secondly, you say "when I have to use it frequently but with different
data". The different data makes no difference to what you need to enter, the
formula remains the same.

Thirdly, when I have a long formula like that that I need to use often, I
either make it into a UDF (which I offered you) or if I want to always do it
in pure Excel functions, I certainly don't retype it every time I want to
use it!!! I store functions like this in a workbook and copy and paste them
to other books whenever I need them. If you always retype formulas then more
fool you!

thanks again for explaining. Infact when cell reference is different
then it would be tidy to change it in formula on frequent basis.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551348

thanks again for explaining. Infact when cell reference is different
then it would be tidy to change it in formula on frequent basis.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551348

starguy wrote:
thanks again for explaining. Infact when cell reference is different
then it would be tidy to change it in formula on frequent basis.

But then you either paste it in with the corrct offset or use Find & Replace