Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 =================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split prefix and suffix | Excel Discussion (Misc queries) | |||
Barcode suffix & Prefix | Excel Programming | |||
How do I set an 'M' prefix to existing text in a column? | New Users to Excel | |||
Prefix and Suffix | Excel Discussion (Misc queries) | |||
adding the same prefix or suffix to a range of cells | Excel Discussion (Misc queries) |