VBA - Remove text between given 2 characters in string

[rsulliva]

I am trying to compare two columns of 1,200 names. The names of some individuals have surnames attached. Ex. (A1: jones-smith,lisa) (B1: jones, lisa a.) Some names have middle initials, spaces, periods and others do not. So Vlookup will not work at this stage. Before breaking the column down using text to columns I would like to run VBA on the column that would I remove all characters including the "-" up to but not including the ",".
I could then break the names down using the text to columns function using a "," and a " " as limits and then recombined and compared without the middle initial using vlookups.

Thanks for your imput.

rsulliva

 

[Rick Rothstein]

Select the column with your names in them, then press Ctrl+H to bring up the Replace dialog box. Type -*, (dash-asterisk-comma) into the "Find what" field, type a comma into the "Replace with" field, then click the "Options>>" button to reveal all the options available and make sure there is no check mark in the "Match entire cell contents" checkbox, then click the "Replace All" button.

 

[rsulliva]

Rick, your simple solution works great. I just recorded the actions using a macro and had it run on both columns before any other actions. Thanks.

 

[Rick Rothstein]

Rick, your simple solution works great. I just recorded the actions using a macro and had it run on both columns before any other actions. Thanks.

Sorry, I know you said you wanted a VBA solution, but I thought this as a "do-it-once-only" operation, so I gave you a non-VBA solution. Your response sounds like the macro you came up with is being run twice somehow. If so, you don't have to do it that way... here is a one-liner macro that will handle both columns at once.

Sub RemoveDashToComma()
  Columns("A:B").Replace "-*,", ",", xlPart
End Sub

 

[sr1111]

Sorry, I know you said you wanted a VBA solution, but I thought this as a "do-it-once-only" operation, so I gave you a non-VBA solution. Your response sounds like the macro you came up with is being run twice somehow. If so, you don't have to do it that way... here is a one-liner macro that will handle both columns at once.

Sub RemoveDashToComma()
  Columns("A:B").Replace "-*,", ",", xlPart
End Sub

How to copy everything between the two 2 characters in sheet2 rows respectively separated by ;

 

[Peter_SSs]

How to copy everything between the two 2 characters in sheet2 rows respectively separated by ;

This is a different question so please start your own thread (and provide some sample data and expected results along with your explanation about what you are trying to do)