How do I cut a portion of text out of a cell?

[jeff]

This is probably an easy question for you experts out there...

I have the following text in cell C3: "This is example text."

I select cell C3 with the mouse, then press F2 to edit, and then select the
word 'example' with the mouse. Here's my problem... I now want to run a macro
(in the Right-Click mouse menu), which will:

1) Cut the selected text out
2) Add the letters "AA" to the beginnning of this cut text
3) Add the letters "BB" to the end of this cut text
4) Reinsert the new text back into the original text, so the text in cell C3
would now read "This is AAexampleBB text."

Any help would be appreciated.
Thanks

 

[Dave Peterson]

When you're editing that cell, no real macros can run.

You may be able to accomplish it another way--if you share the rules of how you
selected that "example" text.

 

[jeff]

It would be selected, while you are in the cell, actually typing in the text,
and you would either hold down shift and use the cursor keys to choose your
text within the cell, or use the mouse to choose the text I want.

Here are more specific details of what I am really trying to accomplish, in
case there is another way to do it:

Basically, we are writing questions, and are exporting them in a text file
(GIFT format) that will be imported into another program. If I BOLD a word in
the cell, when it is exported into the text file it gets stripped of the
BOLD. The program we are importing into uses HTML tags, so while I am
entering my question into Excel, I want to be able to put <strong> on the
left side of my cursor, and </strong> on the right side of cursor. I can then
type the word I want to be BOLDed in between <strong> and </strong>, and when
I export the text file, it will contain this formatting.

 

[Dave Peterson]

I don't think I'd base anything on the actual editing of the value--since no
macro can help with that.

But if there are certain words that always get bolded (and always get bolded
whenever they're used), you could use a macro--essentially a series of
edit|replaces:

select the range
edit|replace
what: _example_ (_ represents a space character)
with: _<strong>example</strong>_
replace all

Or depending on how you generater this GIFT file (never heard of this), you may
be able to do the same thing while you're creating the file.

 

[jeff]

Okay, I think you've answered part of my question... I can't do it the way I
want to do it!!!

How about this instead...

When writing the questions in Excel, I will simply use the actual BOLD
that's in the toolbar, for anything that should be bolded.

Since I'm running a macro to create the .txt file, I'll just 'scan' through
each question first (character by character), and make note of where the
font.bold=true starts and stops. I will then insert <strong> and </strong>
into the cell at the
these points.

Something like this perhaps (I'm new to VBA, so that's why I'm asking). Not
sure what the correct command is to determine the length of a text string in
a cell is, and I'm not sure how to insert text at a certain point within a
string... I'm just making this up off the top of my head...

Range("C3").Select

startBold = 0
startHTML = "<strong>"
stopBold = 0
stopHTML = "</strong>"
txtLength = LEN(C3)

for x = 1 to txtLength
if startBold = 0 then
if (ActiveCell.Character(x,1).font.bold = true) then
startBold = x
end if
end if

if startBold > 0 then
if (ActiveCell.Character(x,1).font.bold = false) then
stopBold = x
end if
end if
next x

txtLength = LEN(startHTML)
ActiveCell.Insert(startHTML).Characters(startBold,1)
ActiveCell.Insert(stopHTML).Characters(stopBold + txtLength,1)


Any pointers would be appreciated.
Thanks

P.S.- GIFT format is just a text file, with a standardized layout for the
importing and exporting of questions & answers. Here's a link if your
interested:

http://moodle.dcu.ie/help.php?module=quiz&file=formatgift.html

 

[Dave Peterson]

This version worked ok for me--ignore the formatting when it's done:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim wks As Worksheet
Dim isBold As Variant
Dim myPfx As String
Dim mySfx As String
Dim inBoldSequence As Boolean
Dim NewString As String
Dim myChar As String

Set wks = Worksheets("Sheet1")
myPfx = "<strong>"
mySfx = "</strong>"

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no text constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
If Len(myCell.Value) = 0 Then
'do nothing
Else
NewString = ""
isBold = myCell.Font.Bold
If isBold = True Then
'whole cell is bolded
NewString = myPfx & myCell.Value & mySfx
ElseIf IsNull(isBold) Then
'mixture of bold and non-bold
inBoldSequence = False
For iCtr = 1 To Len(myCell.Value)
myChar = Mid(myCell, iCtr, 1)
If myCell.Characters(iCtr, 1).Font.Bold = True Then
'found a bold character
If inBoldSequence Then
'do nothing special, just add the character
NewString = NewString & myChar
Else
'turn it on
inBoldSequence = True
'add the prefix
NewString = NewString & myPfx & myChar
End If
Else
'found a regular character
If inBoldSequence Then
'turn it off
inBoldSequence = False
'add the suffix
NewString = NewString & mySfx & myChar
Else
'not in bold, just add the character
NewString = NewString & myChar
End If
End If
Next iCtr
'check to see if last character was bold
If inBoldSequence Then
'if it was, add the suffix
NewString = NewString & mySfx
End If
End If
End If
myCell.Value = NewString
Next myCell
End With

End Sub

Okay, I think you've answered part of my question... I can't do it the way I
want to do it!!!

How about this instead...

When writing the questions in Excel, I will simply use the actual BOLD
that's in the toolbar, for anything that should be bolded.

Since I'm running a macro to create the .txt file, I'll just 'scan' through
each question first (character by character), and make note of where the
font.bold=true starts and stops. I will then insert <strong> and </strong>
into the cell at the
these points.

Something like this perhaps (I'm new to VBA, so that's why I'm asking). Not
sure what the correct command is to determine the length of a text string in
a cell is, and I'm not sure how to insert text at a certain point within a
string... I'm just making this up off the top of my head...

Range("C3").Select

startBold = 0
startHTML = "<strong>"
stopBold = 0
stopHTML = "</strong>"
txtLength = LEN(C3)

for x = 1 to txtLength
if startBold = 0 then
if (ActiveCell.Character(x,1).font.bold = true) then
startBold = x
end if
end if

if startBold > 0 then
if (ActiveCell.Character(x,1).font.bold = false) then
stopBold = x
end if
end if
next x

txtLength = LEN(startHTML)
ActiveCell.Insert(startHTML).Characters(startBold,1)
ActiveCell.Insert(stopHTML).Characters(stopBold + txtLength,1)

Any pointers would be appreciated.
Thanks

P.S.- GIFT format is just a text file, with a standardized layout for the
importing and exporting of questions & answers. Here's a link if your
interested:

http://moodle.dcu.ie/help.php?module=quiz&file=formatgift.html

 

[Dave Peterson]

There's a slight bug in this code.

I added one line.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim wks As Worksheet
Dim isBold As Variant
Dim myPfx As String
Dim mySfx As String
Dim inBoldSequence As Boolean
Dim NewString As String
Dim myChar As String

Set wks = Worksheets("Sheet1")
myPfx = "<strong>"
mySfx = "</strong>"

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no text constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
If Len(myCell.Value) = 0 Then
'do nothing
NewString = myCell.Value '<---Added
Else
NewString = ""
isBold = myCell.Font.Bold
If isBold = True Then
'whole cell is bolded
NewString = myPfx & myCell.Value & mySfx
ElseIf IsNull(isBold) Then
'mixture of bold and non-bold
inBoldSequence = False
For iCtr = 1 To Len(myCell.Value)
myChar = Mid(myCell, iCtr, 1)
If myCell.Characters(iCtr, 1).Font.Bold = True Then
'found a bold character
If inBoldSequence Then
'do nothing special, just add the character
NewString = NewString & myChar
Else
'turn it on
inBoldSequence = True
'add the prefix
NewString = NewString & myPfx & myChar
End If
Else
'found a regular character
If inBoldSequence Then
'turn it off
inBoldSequence = False
'add the suffix
NewString = NewString & mySfx & myChar
Else
'not in bold, just add the character
NewString = NewString & myChar
End If
End If
Next iCtr
'check to see if last character was bold
If inBoldSequence Then
'if it was, add the suffix
NewString = NewString & mySfx
End If
End If
End If
myCell.Value = NewString
Next myCell
End With

End Sub

 

[jeff]

Thanks so much. I'll play with that tomorrow!!!

There's a slight bug in this code.

I added one line.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim wks As Worksheet
Dim isBold As Variant
Dim myPfx As String
Dim mySfx As String
Dim inBoldSequence As Boolean
Dim NewString As String
Dim myChar As String

Set wks = Worksheets("Sheet1")
myPfx = "<strong>"
mySfx = "</strong>"

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no text constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
If Len(myCell.Value) = 0 Then
'do nothing
NewString = myCell.Value '<---Added
Else
NewString = ""
isBold = myCell.Font.Bold
If isBold = True Then
'whole cell is bolded
NewString = myPfx & myCell.Value & mySfx
ElseIf IsNull(isBold) Then
'mixture of bold and non-bold
inBoldSequence = False
For iCtr = 1 To Len(myCell.Value)
myChar = Mid(myCell, iCtr, 1)
If myCell.Characters(iCtr, 1).Font.Bold = True Then
'found a bold character
If inBoldSequence Then
'do nothing special, just add the character
NewString = NewString & myChar
Else
'turn it on
inBoldSequence = True
'add the prefix
NewString = NewString & myPfx & myChar
End If
Else
'found a regular character
If inBoldSequence Then
'turn it off
inBoldSequence = False
'add the suffix
NewString = NewString & mySfx & myChar
Else
'not in bold, just add the character
NewString = NewString & myChar
End If
End If
Next iCtr
'check to see if last character was bold
If inBoldSequence Then
'if it was, add the suffix
NewString = NewString & mySfx
End If
End If
End If
myCell.Value = NewString
Next myCell
End With

End Sub