Search and Replace with ""

[brisardo]

Hello all,

I would like to know if you can help me again.

I download a Excel file which has something like this in many cells:
code.JPG

I want to replace that using a macro to Relationship. I know you can use "" to start the name again but that seems to be not very efficient.

Is there another way to do that?

Maybe have another sheet with the long text in the A:A and the short text in B:B.

Would be great if you could help me. Thanks in advance

[Special-K]

No attachment?

Attach a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[brisardo]

Thanks for the tip. I added a file.

I created a new sheet (search and replace) and maybe it is possible to replace everything in B:B with the data in A:A.

Thank you very much in advance.

[jindon]

Is this what you are after?

Sub test()
    Sheets("raw data").Cells.Replace "<*>", "", 2
End Sub

[brisardo]

Wow this was easy and fast. Thank you very much
Just for interest what does the 2 mean?

[jindon]

The 3 rd argument, "LooAt" named argument, of Replace method has 2 constant values, 1 for xlWhole and 2 for xlPart.
So it replaces "<*>", the every part that begin with "<" and end with ">" with "".

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[jindon]

Delete see below.