Adding Characters

M

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
 
D

Dr. Stephan Kassanke

mcp6453 said:
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
Click to expand...

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
 
M

mcp6453

Dr. Stephan Kassanke said:
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
Click to expand...

Thanks. Worked like a charm.
 
E

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 - (@)
 
D

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
 
D

Dr. Stephan Kassanke

[snip]
Thanks. Worked like a charm.
Click to expand...

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

Stephan
 
Top