Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Editing in Excel
- Thread starter matador
- Start date
Put a blank column to the left and a blank column to the right of your text column. You'll need another blank column, too. So here's your layout:<br><br>Column A is blank. Put a quote mark in the first cell, copy down.<br><br>Column B has your text in it.<br><br>Column C is blank. Put a quote mark in the first cell, copy down.<br><br>Column D is blank. Use this formula and copy it down:<br><br>=a1&b1&c1<br><br>Then, select the cells in D, copy them, don't move cursor! Hit Edit-Paste Special-Values to remove the underlying formula. Then delete columns A, B, and C. <p> <br><a href=mailto: dreamboat@nni.com> dreamboat@nni.com</a><br><a href= </a><br>
Here is a sub that will at the " marks as you type in designated cells. Highlight the cells you want handled and then range name them myrange. Everything will be automatic from then on. <br><br>This will not change text that is already in the cell unless you edit the text. You can hit F2 for cell edit and then hit enter.<br><br>__________________________________<br><br>Private Sub Worksheet_Change(ByVal Target As Excel.Range)<br>'<br>' Add " marks before and after text in myrange<br>' Use F2 (cell edit) and Enter to update text already in the cell<br>'<br>Dim TempString As String<br><br>If Application.Intersect(Target, Range("myrange"
) Is Nothing Then<br> Exit Sub<br>End If<br>If Target.Cells.Count > 1 Then<br> Exit Sub<br>End If<br>If Target.Value = "" Then<br> Exit Sub<br>End If<br><br>Application.EnableEvents = False<br> <br> TempString = Chr(34) & Target.Value & Chr(34)<br> Target.Value = TempString<br><br>Application.EnableEvents = True<br><br>End Sub<br>
) Is Nothing Then<br> Exit Sub<br>End If<br>If Target.Cells.Count > 1 Then<br> Exit Sub<br>End If<br>If Target.Value = "" Then<br> Exit Sub<br>End If<br><br>Application.EnableEvents = False<br> <br> TempString = Chr(34) & Target.Value & Chr(34)<br> Target.Value = TempString<br><br>Application.EnableEvents = True<br><br>End Sub<br> - Status