Can I change a certain part of the formatting within a cell?

I am trying to replace italic text to have square brackets around it instead because I am going to be importing this into a SQL database.  The data is currently in Excel.

For example, the cell is currently Audrey Data Management, but I need it to be Audrey [Data Management].  Is there a way to do this within Excel?  If not, what other options do I have?
Thanks!
SRCLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Add this macro to a general code module. Select the cells to translate and run the macro.

Public Sub Translate()

    Dim Cell As Range
    Dim ItalicsFound As Boolean
    Dim Index As Long
   
    For Each Cell In Selection
        For Index = Cell.Characters.Count To 1 Step -1
            If Cell.Characters(Index, 1).Font.Italic And Not ItalicsFound Then
                Cell.Characters(Index + 1, 0).Insert ("]")
                ItalicsFound = True
            End If
            If Not Cell.Characters(Index, 1).Font.Italic And ItalicsFound Then
                Cell.Characters(Index + 1, 0).Insert ("[")
                ItalicsFound = False
            End If
        Next Index
        Cell.Characters.Font.Italic = False
    Next Cell

End Sub

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smartchapsCommented:
I think there are some bugs in the above code. Below is the modified and corrected version of the same. Check it.

Public Sub Translate2()

    Dim Cell As Range
    Dim ItalicsFound As Boolean
    Dim Index As Long
   
    For Each Cell In Selection
        ItalicsFound = False
        For Index = Cell.Characters.Count To 1 Step -1
            If Cell.Characters(Index, 1).Font.Italic And Not ItalicsFound Then
                Cell.Characters(Index + 1, 0).Insert ("]")
                ItalicsFound = True
            End If
            If Not Cell.Characters(Index, 1).Font.Italic And ItalicsFound Then
                Cell.Characters(Index + 1, 0).Insert ("[")
                ItalicsFound = False
            End If
            If Index = 1 Then
            If Cell.Characters(Index, 1).Font.Italic And ItalicsFound Then
                Cell.Characters(1, 0).Insert ("[")
                ItalicsFound = False
            End If
            End If
        Next Index
        Cell.Characters.Font.Italic = False
    Next Cell

End Sub

Smartchaps
0
zorvek (Kevin Jones)ConsultantCommented:
I made the assumption that the values always start with at least one non-italicized character as shown in the example. If the text CAN start with an italicized character then I would implement as:

Public Sub Translate()

    Dim Cell As Range
    Dim ItalicsFound As Boolean
    Dim Index As Long
   
    For Each Cell In Selection
        For Index = Cell.Characters.Count To 1 Step -1
            If Cell.Characters(Index, 1).Font.Italic And Not ItalicsFound Then
                Cell.Characters(Index + 1, 0).Insert ("]")
                ItalicsFound = True
            End If
            If (Not Cell.Characters(Index, 1).Font.Italic Or Index = 1) And ItalicsFound Then
                Cell.Characters(Index + IIf(Index = 1, 0, 1), 0).Insert ("[")
                ItalicsFound = False
            End If
        Next Index
        Cell.Characters.Font.Italic = False
    Next Cell

End Sub

There is no need to reset ItalicsFound as it always exist the loop in a negative state.

Kevin
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

smartchapsCommented:
I think it takes more time than the solution submitted by me above but works fine.
0
ashwin_mCommented:
Using Excel Formulas........ (this will work if the italics is after a single word)

First select the column range where your data exists.

Now under the data tab, go to "text to columns"
In the dialogue box, select delimited..... and then click next.

Select the "space" box and click next
Then select the cell where you want it to split, and click finish.

Now assume your colum is split into 3 columns...... then use concatenate

=concatenate(a1," ","[",a2," ",a3,"]")

Copy paste this formula in the colum you want the desired output......

See attached file for example.

Regards
Ashwin

italics-to-SQL.xlsx
0
SRCLAuthor Commented:
Thanks for the quick responses!!  
I tried the macros from both zorvek and smartchaps, and they both worked, but they also both failed on a specifc cell.  The cell reads: Pentaerythrite tetranitrate, wetted or Pentaerythritol tetranitrate, wetted, or PETN, wetted with not less than 25 percent water, by mass, or Pentaerythrite tetranitrate, or Pentaerythritol tetranitrate or PETN, desensitized with not less than 15 percent phlegmatizer by mass.  
I just manually updated this one with no issues, but I didn't know if y'all knew why it would fail on that one.
Thanks!
0
zorvek (Kevin Jones)ConsultantCommented:
The code won't handle cells containing more than 255 characters of text. This version will:

Public Sub Translate()

    Dim Cell As Range
    Dim ItalicsFound As Boolean
    Dim Index As Long
    Dim Result As String
   
    For Each Cell In Selection
        Result = vbNullString
        For Index = 1 To Cell.Characters.Count
            If Cell.Characters(Index, 1).Font.Italic And Not ItalicsFound Then
                Result = Result & "[" & Cell.Characters(Index, 1).Text
                ItalicsFound = True
            ElseIf (Not Cell.Characters(Index, 1).Font.Italic Or Index = Cell.Characters.Count) And ItalicsFound Then
                Result = Result & "]" & Cell.Characters(Index, 1).Text
                ItalicsFound = False
            Else
                Result = Result + Cell.Characters(Index, 1).Text
            End If
        Next Index
        Cell.Value = Result
    Next Cell

End Sub

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.