Find and Replace with formatting between two Characters

[jcosman]

Hi all,
I'm looking to find and replace with formatting between two characters. For Example: I want to find all instances of:

|Some Text| and replace it with Some Text.

Basically, it would remove the containing characters "I" and bold the text within these characters.

Any help would be greatly appreciated!

James

 

[crazydragon84]

you just want to remove the first and last letter than bold it? or is it more complex than that? You can use Substitute formula =Substitute(Cell,"I","") and just bold the entire cell?

 

[Scott Huish]

Your post is a little confusing because your sample uses the pipe character |, but then you say replace I ??

 

[Rick Rothstein]

Assuming the delimiter is the pipe symbol (|), give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub BoldPipedText()
  Dim X As Long, PipeLeft As Long, PipeRight As Long, Arr As String, Cell As Range
  For Each Cell In Columns("A").SpecialCells(xlConstants)
    Do
      PipeRight = InStrRev(Cell.Value, "|")
      PipeLeft = InStrRev(Cell.Value, "|", PipeRight - 1)
      Cell.Characters(PipeLeft, PipeRight - PipeLeft + 1).Text = Cell.Characters(PipeLeft + 1, PipeRight - PipeLeft - 1).Text
      Cell.Characters(PipeLeft, PipeRight - PipeLeft - 1).Font.Bold = True
    Loop While InStrRev(Cell.Value, "|")
  Next
End Sub[/td]
[/tr]
[/table]

 

[Tetra201]

Rick,

You can further improve your macro: it currently mishandles a non-between-pipes text in a cell ("ABCDEF" -- > "ABCDEFBCDEF") and throws an RTE 1004 for numbers.

 

[jcosman]

Thanks everybody. It is perhaps a little more complicated than what I wrote above which is why I was hoping a Find and Replace could solve. In reality, I may have a cell that looks like this:

|ABC Company|; Acme Company; |Some other Company|; Bill's Company;

And I would need to transform it to:

ABC Company; Acme Company; Some other Company; Bill's Company;