reverse contents of cell

  • Thread starter Thread starter Eric Blitzer
  • Start date Start date
E

Eric Blitzer

I would like to reverse the order of characters (not sort)
in a cell

ex

47357 to 75374
jrbkl to lkbrj

thanks

Eric
 
Hi
one way: use the following user defined function:
public function reverse_it(rng as range)
dim ret_value
dim str_len as integer
dim i as integer

ret_value=""
str_len = len(rng.value)
if len>0 then
for i = str_len to 1 step -1
ret_value = retvalue + mid(rng.value,i,1)
next
end if
reverse_it=ret_value
end function
 
need to clean up the variables a bit here.

Also, note that for numeric values, the value of the cell will be
reversed, regardless of the format of the cell, so that, for instance if
the cell contains 12.9999999999999997 but is formatted to 0 decimal
places (e.g., 13), the function will return

7999999999999.21

rather than

31

This can be rectified if you use the Text property instead:

Public Function reverse_it(rng As Range)
Dim ret_value
Dim str_len As Integer
Dim i As Integer

ret_value = ""
str_len = Len(rng.Text)
If str_len > 0 Then
For i = str_len To 1 Step -1
ret_value = ret_value + Mid(rng.Text, i, 1)
Next
End If
reverse_it = ret_value
End Function

Also, for WinXL2000 and later, you can use

Public Function reverse_it(rng As Range)
reverse_it = StrReverse(rng.Text)
End Function
 
JE McGimpsey said:
This can be rectified if you use the Text property instead:

Public Function reverse_it(rng As Range) ....
ret_value = ret_value + Mid(rng.Text, i, 1)
Click to expand...
....

Building up by concatenation just rubs the wrong way. Two iteration indices
just feels better.


Function superfoo(r As Range) As String
Dim i As Long, k As Long, n As Long, s As String

s = r.Text
n = Len(s)
superfoo = s
k = n

For i = 1 To n
Mid(superfoo, i, 1) = Mid(s, k, 1)
k = k - 1
Next i
End Function
 
Harlan Grove said:
Building up by concatenation just rubs the wrong way. Two iteration indices
just feels better.
Click to expand...

I agree, this is what I use for XL97/98/01/v.X/04 conditional
compilation:

Public Function StrReverse(sExpression As String) As String
Dim i As Long
Dim j As Long
Dim byArr() As Byte
Dim byRev() As Byte

byArr = sExpression
i = UBound(byArr)
If i > 0 Then
ReDim byRev(i)
For j = 1 To i
byRev(j) = byArr(i)
i = i - 1
Next j
End If
StrReverse = byRev
End Function

Tests faster than using Mid()
 
JE McGimpsey said:
Public Function StrReverse(sExpression As String) As String ....
Dim byArr() As Byte
Dim byRev() As Byte

byArr = sExpression ....
For j = 1 To i
byRev(j) = byArr(i)
i = i - 1
Next j ....
Tests faster than using Mid()
Click to expand...

Something new every day. Thanks for this. I didn't think direct assignment
between strings and arrays of bytes was possible.
 
Harlan Grove said:
Something new every day. Thanks for this. I didn't think direct assignment
between strings and arrays of bytes was possible.
Click to expand...

One note: as written, it has the ability to really mess up Unicode,
since Unicode characters require two bytes.
 
JE McGimpsey said:
One note: as written, it has the ability to really mess up Unicode,
since Unicode characters require two bytes.
Click to expand...

Unicode semantics saves it in some places. Your i > 0 test would fail for
single character strings if string-to-byte-array conversion passed a 1-char
string to a byte array with LBound and UBound 0. If you mean to bypass the
for loop for strings == "", then make that your If condition. Then again,
not much to do for 1-char strings either, so test for Len <= 1. Also
possible to adapt to ASCII or Unicode. And I have feelings about using i as
an iteration bound then manipulating it inside the loop. You're relying on
perverse VB[A] semantics in which

i = 2
For j = 1 To i
'do something
i = i - 1
Next j

passes through the loop twice, the first time with j == 1 and the loop
bounds 1 To 2, and the second time with j == 2 and the loop bounds 1 To 1.
So it seems VB[A] For loops don't constrain the loop variable (j in this
case) to the loop bounds (1 To 1 in the second pass), but continue
incrementing j and only ending the loop when the iteration bounds become 1
To 0 on the third pass. This implies that VB[A] doesn't test whether the
loop variable falls outside its loop bounds as a stopping condition when
either of the loop bounds is manipulated within the loop. Nasty bugs could
come from this!


Public Function sr(s As String) As String
Dim i As Long, j As Long, k As Long, n As Long
Dim inba() As Byte, outba() As Byte

If Len(s) <= 1 Then
sr = s

Else
inba = s
n = UBound(inba) - LBound(inba)
j = n
k = n / Len(s) 's/b 1 for ASCII, 2 for Unicode

ReDim outba(0 To n)

For i = 0 To n Step k
If k = 1 Then
outba(i) = inba(j)
Else
outba(i) = inba(j - 1)
outba(i + 1) = inba(j)
End If
j = j - k
Next i

sr = outba

End If

End Function
 
Harlan Grove said:
perverse VB[A] semantics in which
Click to expand...
....

Nope. Should have done a better job of testing that. Looks like VB[A]
evaluates its For loop bounds at the beginning of the first loop and not
afterwards, so if either loop bounds is a variable, those variables can be
modified within the loop while not affecting how many times the For loop
iterates. Still, it just looks wrong.
 
Back
Top