Insert prefix and suffix to existing string
 

I have multiple columns and rows that need to have text added to the beginning and end of the existing string in the cell. There are too many columns to use a formula in the next column and I cannot use formatting to display the desired text because this file is going to be used by a program other than excel. Is there any way to use Find&Replace to add text before the existing string, then existing cell string, then additional text after existing string?
example:
existing string: 1.24567
want it to say <bol1.24567</bol

the string length will vary and I only want to do it for highlighted cells. Macro might be the way to go but I have limited use with macros.
thanks for your help!

Insert prefix and suffix to existing string
 

On Wed, 28 Nov 2012 00:49:37 +0000, schwachmama wrote:


I have multiple columns and rows that need to have text added to the
beginning and end of the existing string in the cell. There are too
many columns to use a formula in the next column and I cannot use
formatting to display the desired text because this file is going to be
used by a program other than excel. Is there any way to use
Find&Replace to add text before the existing string, then existing cell
string, then additional text after existing string?
example:
existing string: 1.24567
want it to say <bol1.24567</bol

the string length will vary and I only want to do it for highlighted
cells. Macro might be the way to go but I have limited use with macros.
thanks for your help!

A macro is definitely the way to go. The only question will consist of how to select the proper cells to add the tags. You write that those cells are "highlighted". You will need to explain what that means in order for the macro to automatically select the cells.
The example below shows a method, but it will do this for ALL of the cells on the active sheet that contain a constant. Obviously, your definition of "highlighted" and its incorporation into the selection process will be required to narrow that down.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

===================================
Option Explicit
Sub AddString()
Const sBefore As String = "<bol"
Const sAfter As String = "</bol"
Dim r As Range, c As Range

'need to set r equal to the highlighted cells
'below is only an example which sets r to
'every cell containing a constant

Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
For Each c In r
If Left(c.Text, Len(sBefore)) < sBefore Then
c.Value = sBefore & c.Text
End If
If Right(c.Text, Len(sAfter)) < sAfter Then
c.Value = c.Text & sAfter
End If
Next c
End Sub
===================================