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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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!
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
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
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.Itali
Next Cell
End Sub
There is no need to reset ItalicsFound as it always exist the loop in a negative state.
Kevin