Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Replace without macros | Excel Worksheet Functions | |||
Conditional FIND and REPLACE | Excel Worksheet Functions | |||
Create a conditional formula to replace | Excel Worksheet Functions | |||
Is it possible to do a Find/Replace on Conditional Formats? | Excel Worksheet Functions | |||
Replace conditional formatting to use 4 conditions | Excel Programming |