Removing Punctuation from a Single Column

HammerCPA

New Member
Joined
Jun 26, 2012
Messages
2
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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...

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...

Code:
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...

Code:
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, " ", "")
 
Upvote 0
...On the other hand, if it can be any punctuation (dot, comma, colon, space, etc.), then you will need a loop...

Code:
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!
 
Upvote 0

Forum statistics

Threads
1,211,871
Messages
6,104,479
Members
447,911
Latest member
rkramapo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top