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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,047
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,047
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,363
Members
412,320
Latest member
sixnine0312
Top