Question:

Remove certain characters without losing formatting

Problem:

So I have a large set of data, within one of the columns is a data field that I’m needing to italicize based on a certain character. If a word begins with a $ and ends with a & it needs to be italicized for instance a cell could contain the following $Text1& (Text2). After formatting I would need it to look like this: Text1 (Text2). I’ve tried serval diffrent ways to format, but every time I find and replace all the $ and & it either italicizes everything in the cell or nothing at all.

I’ve tried VBA, python, I seem to run into the same issue every time.

Solution:

Assume that in each cell, there is at most one pair markers ($ &), but not more than one.

Option Explicit

Sub demo()

Dim dataRng As Range, c As Range, sTxt

Dim Index1 As Integer, Index2 As Integer

Set dataRng = ActiveSheet.Range("A1:A3") ' Update as needed

For Each c In dataRng

sTxt = c.Text

Index1 = InStr(sTxt, "$")

Index2 = InStr(sTxt, "&")