Add characters in front or behind a cell
 

Hi All

I have two worksheets with the following info.
Column A1: A170 have wordings of different length for both worksheets.

I will like to add 4 extra characters in front for one worksheet and the
opposite for the other worksheet (behind) in each cell.

Thanks

vcff

Answer: Add characters in front or behind a cell
 

Hi vcff,

Sure, I can help you with that. Here's how you can add 4 extra characters in front of the text in Column A1 for one worksheet and add 4 extra characters at the end of the text in Column A1 for the other worksheet:

1. Open the worksheet where you want to add 4 extra characters in front of the text.
2. Select the cell where you want to add the extra characters.
3. Type the following formula in the formula bar:
   Formula:

   ---

   =REPT("text",4)&A1 


   ---

   Replace "text" with the characters you want to add in front of the text. For example, if you want to add "abcd" in front of the text, the formula will be:
   Formula:

   ---

   =REPT("abcd",4)&A1 


   ---

4. Press Enter. The formula will add 4 extra characters in front of the text in the selected cell.
5. Copy the formula to the rest of the cells in Column A1.

Now, to add 4 extra characters at the end of the text in Column A1 for the other worksheet, follow these steps:

1. Open the worksheet where you want to add 4 extra characters at the end of the text.
2. Select the cell where you want to add the extra characters.
3. Type the following formula in the formula bar:
   Formula:

   ---

   =A1&REPT("text",4) 


   ---

   Replace "text" with the characters you want to add at the end of the text. For example, if you want to add "abcd" at the end of the text, the formula will be:
   Formula:

   ---

   =A1&REPT("abcd",4) 


   ---

4. Press Enter. The formula will add 4 extra characters at the end of the text in the selected cell.
5. Copy the formula to the rest of the cells in Column A1.

Add characters in front or behind a cell
 

The best way is to use a fromula in column b to add the extra characters.
You can then copy the changes back to the original column using Copy - Past
Special Values to remove the formula.

To add caharcter it is very simple

="abcd"&A1 to add abcd at the beginning of the data
=A1&"abcd" to add characters at the end of the data

"vcff" wrote:

Hi All

I have two worksheets with the following info.
Column A1: A170 have wordings of different length for both worksheets.

I will like to add 4 extra characters in front for one worksheet and the
opposite for the other worksheet (behind) in each cell.

Thanks

vcff

Add characters in front or behind a cell
 

In an un-used column, say column Z, enter:

=A1 & "qwer" and copy down thru Z170

Then copy Z1 thru Z170 and PasteSpecial/Value back onto column A.

To put the characters before the orginial text, use:

="qwer" & A1
--
Gary''s Student - gsnu200810


"vcff" wrote:

Hi All

I have two worksheets with the following info.
Column A1: A170 have wordings of different length for both worksheets.

I will like to add 4 extra characters in front for one worksheet and the
opposite for the other worksheet (behind) in each cell.

Thanks

vcff

Add characters in front or behind a cell
 

thanks

Have a nice day.

vcff

"Joel" wrote:

The best way is to use a fromula in column b to add the extra characters.
You can then copy the changes back to the original column using Copy - Past
Special Values to remove the formula.

To add caharcter it is very simple

="abcd"&A1 to add abcd at the beginning of the data
=A1&"abcd" to add characters at the end of the data

"vcff" wrote:

Hi All

I have two worksheets with the following info.
Column A1: A170 have wordings of different length for both worksheets.

I will like to add 4 extra characters in front for one worksheet and the
opposite for the other worksheet (behind) in each cell.

Thanks

vcff

Add characters in front or behind a cell
 

Hi,

The suggestions you have recieved are correct, but if you want to add
different characters to each cell then enter the 4 characters in a separate
column such as B1:B179 and in column C
=A1&B1
or
=B1&A1
Copy these down and then copy; edit, paste special, values to get rid of the
formulas.

Of course you can use the Edit, Paste Special, Values technique with either
Joel's or Gary's solutions to eliminate the formula and hense remove the
extra column.

--
Thanks,
Shane Devenshire


"vcff" wrote:

Hi All

I have two worksheets with the following info.
Column A1: A170 have wordings of different length for both worksheets.

I will like to add 4 extra characters in front for one worksheet and the
opposite for the other worksheet (behind) in each cell.

Thanks

vcff

Add characters in front or behind a cell
 

If you want to add the text in place try this macro.

Sub Add_Text()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
If whichside = 1 Then
For Each Cell In thisrng
Cell.Value = moretext & Cell.Value
Next
Else
For Each Cell In thisrng
Cell.Value = Cell.Value & moretext
Next
End If
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord Dibben MS Excel MVP


On Sun, 26 Oct 2008 11:36:01 -0700, ShaneDevenshire
wrote:

Hi,

The suggestions you have recieved are correct, but if you want to add
different characters to each cell then enter the 4 characters in a separate
column such as B1:B179 and in column C
=A1&B1
or
=B1&A1
Copy these down and then copy; edit, paste special, values to get rid of the
formulas.

Of course you can use the Edit, Paste Special, Values technique with either
Joel's or Gary's solutions to eliminate the formula and hense remove the
extra column.