Stripping characters using variable locations

D A

New Member
Joined
Nov 9, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
I found a function on this site that will strip the characters from a string and just leave the numbers. I would like to use variables as the object of the function. My current macro is finding different areas of my spreadsheet that I need to strip off unwanted characters. What I would like to do is use "Item" as my column location and "Rowid" as my row location and store the resulting outcome in Column "AM"+"Rowid. In this example they are specifically Item=2 and Rowid=7 (B7). When I run this it returns 27 in cell "AM7", which is understandable because of the current values of the variables. How do I format the StripChar line to allow me to use the variables? Thank you.

Range("AM" & Rowid).Select
ActiveCell = StripChar(Item & Rowid)


Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
End Function
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,049
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there and welcome to the forum. This should work:
VBA Code:
Range("AM" & Rowid).Select
ActiveCell = StripChar(Cells(Rowid, Item))


Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
End Function
 
Solution

D A

New Member
Joined
Nov 9, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Thank you so much, jmacleary! I know it seems like a simple solution but I tried so many different iterations.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,049
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,220
Messages
5,576,803
Members
412,748
Latest member
MikeyP14
Top