Reversing cell contents in Excel
Dennis Griffiths
I have a column of numbers, i.e.,
12345
1234
123
12
123456
1234567
12345678
123456789
I would like to reverse the cell contents and place the new number into
an adjacent column. Taking the above examples, I would like column B to be:
54321
4321
321
21
654321
7654321
87654321
987654321
Is there a formula to do this in Excel? I have columns of as many as 1000
rows sometimes and I would like to not have to re-enter all of the data in
the second column manually.
Any help will be greatly appreciated.
Thanks........Dennis
--
Posted via CNET Help.com
http://www.help.com/
Ara Pararajasingham
hope it helps
xxxxxxxxxxxxx
Do Until IsEmpty(Range("a1").Offset(k, 0))
adjnewStrg = ""
myStrg = Range("a1").Offset(k, 0).Value
lgth = Len(myStrg)
For j = 0 To lgth - 1
newStrg = Mid(myStrg, lgth - j, 1)
adjnewStrg = adjnewStrg & newStrg
Next j
Range("B1").Offset(k, 0) = adjnewStrg
k = k + 1
Loop
Leo Heuser
This VBA-routine will solve the problem.
1. Enter the VBA-editor with <Alt><F11>
2. Click the project-name in the projects window (upper left of the screen)
3. Choose Insert > Module from the menu line.
4. Insert the code below
Enter yoyr own ranges for ReadRange and WriteRange.
The loop is interrupted, the first time a blank cell is found in A1:A1000.
If this is not, what you want, remove the line:" If c.Value = "" Then Exit For".
5. Run the macro or give it a button.
Best regards
LeoH
Sub ReverseFigures()
'leo.h...@get2net.dk, October 1999
Dim ReadRange As Range
Dim WriteRange As Range
Dim c As Range
Dim OffsetValue As Long
Set ReadRange = Range("A1:A1000")
Set WriteRange = Range("B1:B1000")
OffsetValue = WriteRange.Column - ReadRange.Column
For Each c In ReadRange
If c.Value = "" Then Exit For
c.Offset(0, OffsetValue).Value = StrReverse(c.Value)
Next c
Set ReadRange = Nothing
Set WriteRange = Nothing
Set c = Nothing
End Sub
--------------------------------------
Dennis Griffiths skrev i meddelelsen ...
Harlan Grove
>I would like to reverse the contents of cells in a column in Excel 2000.
>I have a column of numbers, i.e.,
>12345
>1234
>123
>12
>123456
>1234567
>12345678
>123456789
>I would like to reverse the cell contents and place the new number into
>an adjacent column. Taking the above examples, I would like column B to
>be:
>54321
>4321
>321
>21
>654321
>7654321
>87654321
>987654321
<snip>
You've received some macro replies. Let me offer a user-defined function
answer. Since it appears VBA 6 (Excel 2000) provides a StrReverse( )
function in VBA itself, wrap it in a UDF (a simple one offered here).
Function strrev(ByVal s As String) As String
strrev = StrReverse(s)
End Function
For those of us still using Excel 95 or 97, we need to roll our own.
Function strrev(ByVal s As String) As String
Dim i As Long, j As Long, c As Byte
i = 1
j = Len(s)
Do While i < j
c = Asc(Mid(s, i, 1))
Mid(s, i, 1) = Mid(s, j, 1)
Mid(s, j, 1) = Chr(c)
i = i + 1
j = j - 1
Loop
strrev = s
End Function
Put either of these in a VBA module, and use the worksheet formula
=strrev(A1)
to convert (eg) 12345 to 54321.
Leo Heuser
Function strrev(ByVal s As String) As String
Dim Counter As Long
For Counter = 1 To Len(s)
strrev = Mid(s, Counter, 1) & strrev
Next Counter
End Function
Regards
Leo
Harlan Grove
<leo.h...@get2net.dk> writes:
>Or the economy model :-)
>
>Function strrev(ByVal s As String) As String
>Dim Counter As Long
> For Counter = 1 To Len(s)
> strrev = Mid(s, Counter, 1) & strrev
> Next Counter
>End Function
Algorithmically and in terms of lines of code, more economical. In terms of
translated processor op codes and execution time, possibly slower. The function
above involves Len(s) concatenations and some temporary memory manipulation,
and depending on how good VBA's optimization is, possible reevaluation of
Len(s) on each loop. The function below involves Len(s) character fetches and
substring assignments using Mid, like number of integer arithmetic operations,
and half as many Asc and Chr calls each (should be fairly trivial when
operating on byte type variables).
It all depends on how VBA translates both functions into op codes. In other
languages (C, for instance), character interchange is orders of magnitude
faster than string operations involving concatenation and dynamic memory
allocation. Given the slowness of VBA functions relative to built-in functions
and those in compiled XLLs, it's likely there wouldn't be much execution speed
difference between the two, and both would be much slower than the following
array formula using add-in functions from Laurent Longre's MOREFUNC.XLL:
=MCONCAT(MID(s,LEN(s)-INTVECTOR(LEN(s)),1))
Derek Speer
Visual Basic macro.
This works ok on your data, assuming that the first entry is in cell A1. It
terminates when it encounters an empty cell.
Sub test()
Worksheets(1).Activate
Range("A1").Select
x = True
Do While x
If IsEmpty(ActiveCell) Then Stop
data = ActiveCell.Value
Text$ = Application.Text(data, 0)
Out$ = ""
For i = 1 To Len(Text$)
Out$ = Out$ & Mid$(Text$, Len(Text$) + 1 - i, 1)
Next
ActiveCell.Offset(0, 1).Value = Out$
ActiveCell.Offset(1, 0).Activate
Loop
End Sub
Dennis Griffiths wrote:
> I would like to reverse the contents of cells in a column in Excel 2000.
> I have a column of numbers, i.e.,
> 12345
> 1234
> 123
> 12
> 123456
> 1234567
> 12345678
> 123456789
> I would like to reverse the cell contents and place the new number into
> an adjacent column. Taking the above examples, I would like column B to be:
> 54321
> 4321
> 321
> 21
> 654321
> 7654321
> 87654321
> 987654321
> Is there a formula to do this in Excel? I have columns of as many as 1000
> rows sometimes and I would like to not have to re-enter all of the data in
> the second column manually.
> Any help will be greatly appreciated.
> Thanks........Dennis
>
> --
> Posted via CNET Help.com
> http://www.help.com/
--
Derek Speer
Department of Accounting and Finance
The University of Auckland,
Private Bag 92019,
Auckland, New Zealand.
Voice phone (649) 373-7599 Extn 7290
Fax (649) 373-7406