Removing Punctuation from a Single Column

[HammerCPA]

Hi, I am using this function in a single column to reverse the contents of another cell.
"Option Explicit
Function ReverseCell(Rcell As Range, Optional IsText As Boolean)
Dim i As Integer
Dim StrNewNum As String
Dim strOld As String
strOld = Trim(Rcell)
For i = 1 To Len(strOld)
StrNewNum = Mid(strOld, i, 1) & StrNewNum
Next i
If IsText = False Then
ReverseCell = CLng(StrNewNum)
Else
ReverseCell = StrNewNum
End If
End Function"

Is there something I can add here to remove the punctuation of the cell being copied and reversed in the target cell?
IE 24.17f9 is in cell a6, using this it is 9f71.42 in cell s6. I want cell s6 to read 9f7142.

Thanks in advance.

 

[Rick Rothstein]

Hi, I am using this function in a single column to reverse the contents of another cell.
"Option Explicit
Function ReverseCell(Rcell As Range, Optional IsText As Boolean)
Dim i As Integer
Dim StrNewNum As String
Dim strOld As String
strOld = Trim(Rcell)
For i = 1 To Len(strOld)
StrNewNum = Mid(strOld, i, 1) & StrNewNum
Next i
If IsText = False Then
ReverseCell = CLng(StrNewNum)
Else
ReverseCell = StrNewNum
End If
End Function"

Is there something I can add here to remove the punctuation of the cell being copied and reversed in the target cell?
IE 24.17f9 is in cell a6, using this it is 9f71.42 in cell s6. I want cell s6 to read 9f7142.

Thanks in advance.

First off, I need to tell you that VB has a built-in function to reverse text... StrReverse, so you can reverse the contents (still with the punctuation) with a single line of code...

ReverseCell = StrReverse(Rcell.Value)

As for removing the punctuation... is it always going to be a dot? If so, you can just replace it with nothing...

ReverseCell = Replace(StrReverse(Rcell.Value), ".", "")

On the other hand, if it can be any punctuation (dot, comma, colon, space, etc.), then you will need a loop...

ReverseCell = StrReverse(Rcell.Value)
For X = 1 To Len(ReverseCell)
  If Mid(ReverseCell, X, 1) Like "[!A-Za-z0-9]" Then Mid(ReverseCell, X) = " "
Next
ReverseCell = Replace(ReverseCell, " ", "")

 

[HammerCPA]

...On the other hand, if it can be any punctuation (dot, comma, colon, space, etc.), then you will need a loop...

ReverseCell = StrReverse(Rcell.Value)
For X = 1 To Len(ReverseCell)
  If Mid(ReverseCell, X, 1) Like "[!A-Za-z0-9]" Then Mid(ReverseCell, X) = " "
Next
ReverseCell = Replace(ReverseCell, " ", "")

Works great. Exactly what I was looking for. Thank you!