Removing text between characters, with multiple instances per line?

[SatsuMX]

Hi guys,

I'm looking to cleanup data that we are importing from Sharepoint, that comes out like this:

S, Alicia;#39;#A, Elizabeth;#121;#C, Luis;#13

As you can see, there is a pattern between the # symbols, so in this case, the desired output would be:

S, Alicia;A, Elizabeth;C, Luis;

This would be simple if there was only one instance per line, but it repeats, and the number varies in length. My first thought is that this definitely needs VBA, but I'm not sure where to start. Suggestions?

Thank you!

 

[Rick Rothstein]

Give this a try...

1) Select the column or columns with those entries

2) Press CTRL+H to bring up the "Find and Replace" dialog box

3) Type #*# in the "Find what" field

4) Make sure the "Replace with" field is empty

5) Click the "Options>>" button and make sure the "Match entire cell contents" check box is not checked

6) Click the "Replace All" button

7) Change the "Find what" field to #*

8) Click the "Replace All" button again

9) Click the "Close" button

10) Click any cell to remove the full column selection

Your data should now be in the format you want

 

[JLGWhiz]

Hi guys,

I'm looking to cleanup data that we are importing from Sharepoint, that comes out like this:

S, Alicia;#39;#A, Elizabeth;#121;#C, Luis;#13

As you can see, there is a pattern between the # symbols, so in this case, the desired output would be:

S, Alicia;A, Elizabeth;C, Luis;

This would be simple if there was only one instance per line, but it repeats, and the number varies in length. My first thought is that this definitely needs VBA, but I'm not sure where to start. Suggestions?

Thank you!

You can do this by selecting the entire range with the entries you want to mocify, then on the ribbon under Find&Select, choose 'Replace'. In the dialog box
Replace What: #
Replace With: ""

 

[Rick Rothstein]

You can do this by selecting the entire range with the entries you want to mocify, then on the ribbon under Find&Select, choose 'Replace'. In the dialog box
Replace What: #
Replace With: ""

That is not sufficient as it will leave the numbers between the semi-colons. I am pretty sure the simplest way to get what the OP wants is by using the method I posted in Message #2 .

 

[SatsuMX]

Thank you for the replies, and I apologize, I forgot to specify:

I need this to be automated, as the data is refreshed every time the workbook is opened. Sorry!

 

[Rick Rothstein]

Thank you for the replies, and I apologize, I forgot to specify:

I need this to be automated, as the data is refreshed every time the workbook is opened. Sorry!

Give this macro a try...

Sub RemoveNumberAndPoundSigns()
  Columns("F").Replace "#*#", "", xlPart, , , , False, False
  Columns("F").Replace "#*", ""
End Sub

 

[SatsuMX]

Thank you Rick, will try it out today and report back with you.