Conditional replace
 

I am looking to search one column for "V" in a string.

When there is a letter preceding the "V" I need to add "-" between the
letter and the "V".
When there is a number preceding the "V" then I need it to leave it like it
is.

Results need to look like this.

old new
1MSV01 1MS-V01
1MS4V01 1MS4V01

Anyone have any ideas how I could do this quickly. I have several sheets
with several thousand records each.

Conditional replace
 

Ctrl+H
Find What = "V"
Replace With = "-V"
Expand options
Within = Workbook
Match Case = True

Click "Replace All"

Charles Chickering
Mikey B wrote:
I am looking to search one column for "V" in a string.

When there is a letter preceding the "V" I need to add "-" between the
letter and the "V".
When there is a number preceding the "V" then I need it to leave it like it
is.

Results need to look like this.

old new
1MSV01 1MS-V01
1MS4V01 1MS4V01

Anyone have any ideas how I could do this quickly. I have several sheets
with several thousand records each.

Conditional replace
 

Ok, thanks for your reply but it does not actually do what I was looking for.
The reason why I did not use the normal find/replace is that this results in
adding a dash in front of the "V" in all cases including when numbers precede
the "V" which is what I am trying to avoid.
I thought there might have been a way to do this programatically using a
if...then statement.

Also, in some cases the dash is already already correctly in place and
adding another dash would not be wanted.

I appreciate any help.

"Die_Another_Day" wrote:

Ctrl+H
Find What = "V"
Replace With = "-V"
Expand options
Within = Workbook
Match Case = True

Click "Replace All"

Charles Chickering
Mikey B wrote:
I am looking to search one column for "V" in a string.

When there is a letter preceding the "V" I need to add "-" between the
letter and the "V".
When there is a number preceding the "V" then I need it to leave it like it
is.

Results need to look like this.

old new
1MSV01 1MS-V01
1MS4V01 1MS4V01

Anyone have any ideas how I could do this quickly. I have several sheets
with several thousand records each.

Conditional replace
 

Try this subroutine.
Sub ReplaceAll()
Dim cnt As Long
Dim Vpos As Long 'V position
Dim CrCl As Range 'Current Cell
Dim WS As Worksheet
Const Cmn As String = "A"
For Each WS In ActiveWorkbook.Sheets
For cnt = 1 To WS.Range(Cmn & Rows.Count).End(xlUp).Row
Set CrCl = WS.Range(Cmn & cnt)
Vpos = InStr(1, CrCl, "V")
If Vpos 0 Then
If Not IsNumeric(Mid(CrCl, Vpos - 1, 1)) Then
CrCl.Value = Left(CrCl, Vpos - 1) & "-" & _
Right(CrCl, Len(CrCl) - Vpos + 1)
End If
End If
Next
Next
End Sub

Charles

Mikey B wrote:
Ok, thanks for your reply but it does not actually do what I was looking for.
The reason why I did not use the normal find/replace is that this results in
adding a dash in front of the "V" in all cases including when numbers precede
the "V" which is what I am trying to avoid.
I thought there might have been a way to do this programatically using a
if...then statement.

Also, in some cases the dash is already already correctly in place and
adding another dash would not be wanted.

I appreciate any help.

"Die_Another_Day" wrote:

Ctrl+H
Find What = "V"
Replace With = "-V"
Expand options
Within = Workbook
Match Case = True

Click "Replace All"

Charles Chickering
Mikey B wrote:
I am looking to search one column for "V" in a string.

When there is a letter preceding the "V" I need to add "-" between the
letter and the "V".
When there is a number preceding the "V" then I need it to leave it like it
is.

Results need to look like this.

old new
1MSV01 1MS-V01
1MS4V01 1MS4V01

Anyone have any ideas how I could do this quickly. I have several sheets
with several thousand records each.

Conditional replace
 

Thanks Charles. This works great.

"Die_Another_Day" wrote:

Try this subroutine.
Sub ReplaceAll()
Dim cnt As Long
Dim Vpos As Long 'V position
Dim CrCl As Range 'Current Cell
Dim WS As Worksheet
Const Cmn As String = "A"
For Each WS In ActiveWorkbook.Sheets
For cnt = 1 To WS.Range(Cmn & Rows.Count).End(xlUp).Row
Set CrCl = WS.Range(Cmn & cnt)
Vpos = InStr(1, CrCl, "V")
If Vpos 0 Then
If Not IsNumeric(Mid(CrCl, Vpos - 1, 1)) Then
CrCl.Value = Left(CrCl, Vpos - 1) & "-" & _
Right(CrCl, Len(CrCl) - Vpos + 1)
End If
End If
Next
Next
End Sub

Charles

Mikey B wrote:
Ok, thanks for your reply but it does not actually do what I was looking for.
The reason why I did not use the normal find/replace is that this results in
adding a dash in front of the "V" in all cases including when numbers precede
the "V" which is what I am trying to avoid.
I thought there might have been a way to do this programatically using a
if...then statement.

Also, in some cases the dash is already already correctly in place and
adding another dash would not be wanted.

I appreciate any help.

"Die_Another_Day" wrote:

Ctrl+H
Find What = "V"
Replace With = "-V"
Expand options
Within = Workbook
Match Case = True

Click "Replace All"

Charles Chickering
Mikey B wrote:
I am looking to search one column for "V" in a string.

When there is a letter preceding the "V" I need to add "-" between the
letter and the "V".
When there is a number preceding the "V" then I need it to leave it like it
is.

Results need to look like this.

old new
1MSV01 1MS-V01
1MS4V01 1MS4V01

Anyone have any ideas how I could do this quickly. I have several sheets
with several thousand records each.