Solved ExcelVBA Q Re: Replacing first instance of a character

January 22, 2020 at 10:17:54
Specs: Windows 10
I have a column with long strings of text and numbers that I need to clean up with VBA code. The data has “/” separators within the string and I need to replace the first “/” with “.” and then delete the second “/” and everything that follows it.

Example of strings:
(Desired result = “'text1inc4.4r_2”)
(Desired result = “'ex1text1.”)
(Desired result = “ex2_sp6.1.6”)
(Desired result = “ex3s4_hst.cnss.”)

The below code will delete everything after the first “/” so I need to add preceding code to replace or substitute the first “/” with a “.” (I know several ways to accomplish this with Excel formulas, but need to have it done with VBA code instead.) The string length and content varies between the slashes

Code so far …

For Each c In Range("Y2:Y" & LngLast)
If InStr(c.Value, "/") Then
c.Value = Left(c.Value, InStr(c.Value, "/") - 1)
End If
Next c

message edited by User444

See More: ExcelVBA Q Re: Replacing first instance of a character

Reply ↓  Report •

January 22, 2020 at 11:09:29
✔ Best Answer
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!

As for your question, try this:

Sub AlterText2()
  For Each c In Range("Y2:Y" & 5)
     c.Value = Replace(Left(c.Value, InStr(InStr(1, c.Value, "/") + 1, c.Value, "/") - 1), "/", ".")
  Next c
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Reply ↓  Report •

January 22, 2020 at 11:42:20
Awesome, thanks so much DerbyDad03, that works perfectly!! :-)

Reply ↓  Report •
Related Solutions

Ask Question