Link to home
Start Free TrialLog in
Avatar of SRCL
SRCL

asked on

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!
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of smartchaps
smartchaps

I think it takes more time than the solution submitted by me above but works fine.
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
Avatar of SRCL

ASKER

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!
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