Adding Characters

[mcp6453]

In Excel 2000, I have a column of names to which I need to add a "("
before and a ")" after. That is,

Your Name Here

becomes

(Your Name Here).

How can I make that change on a LONG list of names?

Thanks

[Dr. Stephan Kassanke]

"mcp6453" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> In Excel 2000, I have a column of names to which I need to add a "("
> before and a ")" after. That is,
>
> Your Name Here
>
> becomes
>
> (Your Name Here).
>
> How can I make that change on a LONG list of names?
>
> Thanks

Hi mcp6453 (real name would be nice),

add the following formula in a column right to your name column (names
starting in Column A)

B1: ="(" & A1 & ")"

and copy that formula down (e.g. by dragging down the small rectangle of
cell B1)

Stephan

[mcp6453]

Dr. Stephan Kassanke wrote:

> Hi mcp6453 (real name would be nice),
>
> add the following formula in a column right to your name column (names
> starting in Column A)
>
> B1: ="(" & A1 & ")"
>
> and copy that formula down (e.g. by dragging down the small rectangle of
> cell B1)
>
> Stephan
>
>

Thanks. Worked like a charm.

[Earl Kiosterud]

mcp,

The formula method suggested by Stephan makes your data appear in another
cell with the parentheses. You can permanently change the original column
by selecting and copying the formula column, then paste over the original
column with Paste Special - Values. You no longer need the helper column,
and can delete it, but any new names will need to have the process applied.

Another way is to put the parentheses in with formatting. The parens won't
actually be in the cells, so any formula that makes reference to them won't
get the parens. It just makes them appear in the cell thus formatted:
Select the column, Format - Cells - Number - Custom - (@)
--
Earl Kiosterud
www.smokeylake.com

"mcp6453" <[email protected]> wrote in message
news:[email protected]...
> In Excel 2000, I have a column of names to which I need to add a "("
> before and a ")" after. That is,
>
> Your Name Here
>
> becomes
>
> (Your Name Here).
>
> How can I make that change on a LONG list of names?
>
> Thanks

[David McRitchie]

I like the format idea, then nothing actually has been changed.

Another way would be to use a macro, which beats the use of
a formula, followed by copy, paste special, values -- especially if
this is going to be done on a regular basis.

Sub Enclose_Text_cells()
Dim tstRange As Range, cell As Range
On Error Resume Next
Set tstRange = Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
On Error GoTo 0
If tstRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cell In tstRange
cell.Value = "(" & Trim(cell.Formula) & ")"
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Directions to install and use a macro in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Earl Kiosterud" <[email protected]> wrote ...
> Another way is to put the parentheses in with formatting. The parens won't
> actually be in the cells, so any formula that makes reference to them won't
> get the parens. It just makes them appear in the cell thus formatted:
> Select the column, Format - Cells - Number - Custom - (@)

[Dr. Stephan Kassanke]

"mcp6453" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...

[snip]

>>
>
> Thanks. Worked like a charm.

Your are welcome. I like the format approach Earl suggested. Elegant and no
need for the helper column.

Stephan