Reverse contents of a cell

[PhatPipe]

In excel:
I need to transpose numbers within a cell
i.e.
01101000

I have eight numbers in one cell and I want to reverse them like

00010110
Like reading from right to left then reversing it.

 

[DaleWatson123321]

I'm not sure, based on your example, where you want the "split" to take place, but here's a formula that "might work", or one that you can alter to suit your needs...

=RIGHT(A1,4)&LEFT(A1,4)

Hope it helps.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[rmikesmith]

PhatPipe,

Try the following. There are 3 versions of the same code. The Sub that takes an argument is best called from another procedure; the Sub w/o an argument uses the active cell and could be assigned to a command button. The Function version can be used as a worksheet function.
One caveat: If a cell contains a number such as 10000 and is formatted General or some numeric format, the reversed contents will not display properly; in this example, the result would be 1. Format the cell as Text to avoid this.

Sub ReverseCell(ByRef oCell As Range)
Dim i As Integer
Dim impStr As String
Dim outStr As String

impStr = oCell.Value
For i = Len(impStr) To 1 Step -1
  outStr = outStr & Mid$(impStr, i, 1)
Next i
oCell.Value = outStr
End Sub


Sub ReverseCell2()
Dim i As Integer
Dim impStr As String
Dim outStr As String

impStr = ActiveCell.Value
For i = Len(impStr) To 1 Step -1
  outStr = outStr & Mid$(impStr, i, 1)
Next i
ActiveCell.Value = outStr
End Sub


Function ReverseCell3(ByVal oCell As Range) As String
Dim i As Integer
Dim impStr As String
Dim outStr As String

impStr = oCell.Value
For i = Len(impStr) To 1 Step -1
  outStr = outStr & Mid$(impStr, i, 1)
Next i
ReverseCell3 = outStr
End Function

HTH

Mike

 

[xlbo]

Just as an adendum to this, the numbers that PhatPie is showing are clearly formatted as text, therefore, using the value operator will truncate them if they have zeros at the start
try this:
Function Reverse(mRange As Range)
Dim textToReverse As String
textToReverse = mRange.Text
x = ""
For i = Len(textToReverse) To 1 Step -1
y = Mid(textToReverse, i, 1)
x = x & y
Next i
Reverse = x
End Function
HTH
Geoff

 

[rmikesmith]

Geoff,

Interestingly enough, I originally used the Text property (not sure why I changed it). However, when I use the as-posted version of the function on a cell containing 11010000 it returns 00001011 in a cell formatted General.

Regards,
Mike

 

[xlbo]

Interesting indeed, I'd have thought that
impStr = oCell.value
would truncate 01101000 to 1101000 at least
but you are quite right - when I substitute .Value, for .Text, not only does excel not complain that I'm assigning a value to a string defined variable, but it gives exactly the same result as .Text
Must be that the .Value property of a text formatted cell is the same as the .Text property
Definite implications for this I'd have thought...
Geoff

 

[PhatPipe]

Thanks, I got the second example to work on a cell

I don't know how to put the other examples in?
(Not really good at Excel)

My values are in C12, D12, E12, etc.
I would like to have the function operate on several cells

But I guess I'll have to study some more .....

Thanks for the Help.

 

[PhatPipe]

Got the third example to work

This will do the job!

Still do not understand how to get the first example to work?

Thanks again!

 

[rmikesmith]

PhatPipe,

I assume you're referring to my three examples. Exampe 1, procedure ReverseCell, needs to be called from within another VBA procedure. Here is an example:

Sub ApplyReverse()
Dim Rng as Range
dim OneCell as Range

  Set Rng = ActiveSheet.Range("C12:E12")
  For Each OneCell In Rng
    ReverseCell OneCell
  Next OneCell

End Sub

HTH

Mike

 

[DaleWatson123321]

Hi Guys,

The =TEXT function seems like it might work well together with my original example ??? ...

=TEXT(RIGHT(A1,4)&LEFT(A1,4),"00000000&quot

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Hi Dale - the original question was to reverse the contents ie turn 123456789 into 987654321. Surely, your example will give 67891234.....
Geoff

 

[DaleWatson123321]

Geoff,

Daaaaah ...sometimes it helps to be "hit on the head" with the obvious. THANKS for getting my attention !!!

How about THIS formula...

=TEXT(MID(A2,LEN(A2),1)&MID(A2,LEN(A2)-1,1)&MID(A2,LEN(A2)-2,1)&MID(A2,LEN(A2)-3,1)&MID(A2,LEN(A2)-4,1)&MID(A2,LEN(A2)-5,1)&MID(A2,LEN(A2)-6,1)&MID(A2,LEN(A2)-7,1),"00000000&quot

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

No worries Dale - think it's kinda outweighed by the enormous number of peeps you've helped
Monster formula tho
;-)
G