VBA Replace Hyphens in a String Up to a Position

[beartooth91]

Well, have been struggling with various combinations of Replace and Mid to delete the hyphens in a string while leaving the last one, if its present.

The data to search and replace is in one of the following forms and can be up to a maximum of 20 characters:

1-SYS-CC-0001 #Result should be 1SYSCC0001

1-SYS-CC-0002-A #Result should be 1SYSCC0002-A

1SYSCCTEXT-FCT # Result should be the same, unchanged

I believe what I'm looking for is to search the first 9-10 characters in the string and delete all hyphens within those first 9-10 characters. I just haven't been able to come up with the correct VBA to do it.

Any ideas? Thanks.

 

[lrobbo314]

How about this?

Web Intelligence - 2025-02-19T071707.591.xlsx
	A	B
1	1-SYS-CC-0001	1SYSCC0001
2	1-SYS-CC-0002-A	1SYSCC0002-A
3	1SYSCCTEXT-FCT	1SYSCCTEXT-FCT
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=SUBSTITUTE(LEFT(A1,10),"-","")&RIGHT(A1,LEN(A1)-10)

 

[beartooth91]

How about this?

Web Intelligence - 2025-02-19T071707.591.xlsx
	A	B
1	1-SYS-CC-0001	1SYSCC0001
2	1-SYS-CC-0002-A	1SYSCC0002-A
3	1SYSCCTEXT-FCT	1SYSCCTEXT-FCT
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=SUBSTITUTE(LEFT(A1,10),"-","")&RIGHT(A1,LEN(A1)-10)

This may work if I can do it with VBA.....

 

[HighAndWilder]

Well, have been struggling with various combinations of Replace and Mid to delete the hyphens in a string while leaving the last one, if its present.

The data to search and replace is in one of the following forms and can be up to a maximum of 20 characters:

1-SYS-CC-0001 #Result should be 1SYSCC0001

1-SYS-CC-0002-A #Result should be 1SYSCC0002-A

1SYSCCTEXT-FCT # Result should be the same, unchanged

I believe what I'm looking for is to search the first 9-10 characters in the string and delete all hyphens within those first 9-10 characters. I just haven't been able to come up with the correct VBA to do it.

Any ideas? Thanks.

Does this work in VBA with all scenarios?

Sub test()
Dim s As String

  ' Try this next line where s is the value.
  ' Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & Mid(s, InStrRev(s, "-") + 1)

  s = "1-SYS-CC-0001"
   
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCC0001"
 
  s = "1-SYS-CC-0002-A"
 
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCC0002-A"
 
  s = "1SYSCCTEXT-FCT"
 
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCCTEXT-FCT"
 
End Sub

 

[beartooth91]

Does this work in VBA with all scenarios?

Sub test()
Dim s As String

  ' Try this next line where s is the value.
  ' Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & Mid(s, InStrRev(s, "-") + 1)

  s = "1-SYS-CC-0001"
  
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCC0001"
 
  s = "1-SYS-CC-0002-A"
 
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCC0002-A"
 
  s = "1SYSCCTEXT-FCT"
 
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCCTEXT-FCT"
 
End Sub

This won't work because I gave you examples and not actuals (my fault for not saying that above). The SYS and CC codes change depending on the system and instrument type. See next post below.

 

[HighAndWilder]

This won't work because I gave you examples and not actuals (my fault for not saying that above). The SYS and CC codes change depending on the system and instrument type. See next post below.

My VBA solution does not look at the contents of the string, just actual positions and whether or not the last character is numeric. Give it a try.

 

[beartooth91]

How about this?

Web Intelligence - 2025-02-19T071707.591.xlsx
	A	B
1	1-SYS-CC-0001	1SYSCC0001
2	1-SYS-CC-0002-A	1SYSCC0002-A
3	1SYSCCTEXT-FCT	1SYSCCTEXT-FCT
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=SUBSTITUTE(LEFT(A1,10),"-","")&RIGHT(A1,LEN(A1)-10)

As in my earlier reply, this needs to be VBA. That said, your reply got me to thinking and I came up with:

Sub Remove_Hyphens()
'Removes hyphens from the Component Numbers in Column B

With Worksheets(1)

 Dim LastRow&, a&, x&, searchPos&, newStr$, oldStr$, lStr$, rStr$
 
 LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
 
 For x = 11 To LastRow
 '  cell.Value = Replace(cell.Value, "-", "", , 2)
   oldStr = Cells(x, "B").Value
   searchPos = InStr(1, oldStr, "-")
   lStr = Replace(Left(oldStr, 9), "-", "")
   rStr = Mid(oldStr, 10)
   newStr = lStr & rStr
   If newStr <> oldStr Then
     .Cells(x, "B").EntireRow.Interior.ColorIndex = 20
     .Cells(x, "B").Value = newStr
     a = a + 1
   End If
   
  Next x
 
 If a > 0 Then
   MsgBox ("One or more point names were changed. Please review the Lt Blue highlighted rows.")
   
 End If
  
End With
  
End Sub

- And it seems to work though I don't want to mark it as a solution. Plus, I'm sure there's easier ways to do this.

 

[HighAndWilder]

As in my earlier reply, this needs to be VBA. That said, your reply got me to thinking and I came up with:

Sub Remove_Hyphens()
'Removes hyphens from the Component Numbers in Column B

With Worksheets(1)

 Dim LastRow&, a&, x&, searchPos&, newStr$, oldStr$, lStr$, rStr$
 
 LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
 
 For x = 11 To LastRow
 '  cell.Value = Replace(cell.Value, "-", "", , 2)
   oldStr = Cells(x, "B").Value
   searchPos = InStr(1, oldStr, "-")
   lStr = Replace(Left(oldStr, 9), "-", "")
   rStr = Mid(oldStr, 10)
   newStr = lStr & rStr
   If newStr <> oldStr Then
     .Cells(x, "B").EntireRow.Interior.ColorIndex = 20
     .Cells(x, "B").Value = newStr
     a = a + 1
   End If
  
  Next x
 
 If a > 0 Then
   MsgBox ("One or more point names were changed. Please review the Lt Blue highlighted rows.")
  
 End If
 
End With
 
End Sub

- And it seems to work though I don't want to mark it as a solution. Plus, I'm sure there's easier ways to do this.

Can you post some representative data so that we can do some tests?

 

[beartooth91]

Does this work in VBA with all scenarios?

Sub test()
Dim s As String

  ' Try this next line where s is the value.
  ' Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & Mid(s, InStrRev(s, "-") + 1)

  s = "1-SYS-CC-0001"
 
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCC0001"
 
  s = "1-SYS-CC-0002-A"
 
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCC0002-A"
 
  s = "1SYSCCTEXT-FCT"
 
  MsgBox Replace(Left(s, InStrRev(s, "-")), "-", "") & Left("-", Abs(Not (IsNumeric(Right(s, 1))))) & _
    Mid(s, InStrRev(s, "-") + 1), vbOKOnly, s & "  " & "1SYSCCTEXT-FCT"
 
End Sub

Tested and did not work. Inserted these two point names -

1-CTC-FCT-0002-XI
1-CTC-FCT-0001-HS

- and it left them both unchanged
- BTW, all of these to evaluate are in B11:B & LastRow

 

[HighAndWilder]

Tested and did not work. Inserted these two point names -

1-CTC-FCT-0002-XI
1-CTC-FCT-0001-HS

- and it left them both unchanged

So what is the logic that you want to apply?

We either need an explanation of the logic or representative examples of the data, all scenarios, and what you expect for each scenario.