How do I cut a portion of text out of a cell?
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 |
How do I cut a portion of text out of a cell?
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 wrote: 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 |
How do I cut a portion of text out of a cell?
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" wrote: 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 wrote: 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 |
How do I cut a portion of text out of a cell?
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: _<strongexample</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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
How do I cut a portion of text out of a cell?
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...ormatgift.html "Dave Peterson" wrote: 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: _<strongexample</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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
How do I cut a portion of text out of a cell?
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 jeff wrote: 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...ormatgift.html "Dave Peterson" wrote: 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: _<strongexample</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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
How do I cut a portion of text out of a cell?
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 Dave Peterson wrote: This version worked ok for me--ignore the formatting when it's done: |
How do I cut a portion of text out of a cell?
Thanks so much. I'll play with that tomorrow!!!
"Dave Peterson" wrote: 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 Dave Peterson wrote: This version worked ok for me--ignore the formatting when it's done: |
All times are GMT +1. The time now is 03:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com