I need a marco that can be use to delete the name in a cell in the
whole column and keep the number only (4042989)

A
1 FRED ELMS (4042989)
2 CLIFFORD LEE (I-4042705)
3 HOANG HUYNH (4044099)
4 BRENT FAUGHT (4043945)
5 ELIZABETH LAND (4044665)
6 TIFFIN FRIESE (4044885)
7 TIFFIN FRIESE (4044885)
8 FRANK KOLARZ (4044048)
9 ALEJANDRO SANCHEZ (4041452)
10 BRENT RAMOS (4045946)
11 BETTY EHMEN (4045474)
12 BRANDA REAVES (4045546)
13 JAMES BISHOP (4045820)
14 ELIZABETH BURKE (4045844)
15 MORRIS DEVORE (4045835)
16 JULIE RENSHAW (4044396)
17 WILLIAM THOMASON (4044207)
18 GEORGE FOBIA (4044664)


Any help would be appreciated.

Chuong Nguyen

It looks like you could record a macro when you do:

Select column A
edit|Replace
what: * ( <-- that's an asterisk, space character, open paren
with: ( <-- that's just the open paren
replace all

You may want to look at your earlier post for a similar solution to the created:
question.



Chuong Nguyen wrote:
>
> I need a marco that can be use to delete the name in a cell in the
> whole column and keep the number only (4042989)
>
> A
> 1 FRED ELMS (4042989)
> 2 CLIFFORD LEE (I-4042705)
> 3 HOANG HUYNH (4044099)
> 4 BRENT FAUGHT (4043945)
> 5 ELIZABETH LAND (4044665)
> 6 TIFFIN FRIESE (4044885)
> 7 TIFFIN FRIESE (4044885)
> 8 FRANK KOLARZ (4044048)
> 9 ALEJANDRO SANCHEZ (4041452)
> 10 BRENT RAMOS (4045946)
> 11 BETTY EHMEN (4045474)
> 12 BRANDA REAVES (4045546)
> 13 JAMES BISHOP (4045820)
> 14 ELIZABETH BURKE (4045844)
> 15 MORRIS DEVORE (4045835)
> 16 JULIE RENSHAW (4044396)
> 17 WILLIAM THOMASON (4044207)
> 18 GEORGE FOBIA (4044664)
>
> Any help would be appreciated.
>
> Chuong Nguyen

--

Dave Peterson

Try some code like the following. Select the cells to change and then run
the code.

Sub AAA()
Dim R As Range
Dim Pos As Long
Dim S As String
Application.EnableEvents = False
On Error GoTo ERRH:
For Each R In Selection.Cells
If R.HasFormula = False Then
Pos = InStr(1, R.Text, "(", vbBinaryCompare)
If Pos = 0 Then
S = R.Text
Else
S = Mid(R.Text, Pos)
'''''''''''''''''''''''''''''
' If you want to get rid of
' the parentheses, uncomment
' the next two lines. Note,
' though, that Excel will
' treat a number within ()
' as a negative number.
''''''''''''''''''''''''''''
'S = Replace(S, "(", vbNullString)
'S = Replace(S, ")", vbNullString)
End If
R.Value = S
End If
Next R
ERRH:
Application.EnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

"Chuong Nguyen" <(E-Mail Removed)> wrote in message
news:OZWMCU%(E-Mail Removed)...
> I need a marco that can be use to delete the name in a cell in the
> whole column and keep the number only (4042989)
>
> A
> 1 FRED ELMS (4042989)
> 2 CLIFFORD LEE (I-4042705)
> 3 HOANG HUYNH (4044099)
> 4 BRENT FAUGHT (4043945)
> 5 ELIZABETH LAND (4044665)
> 6 TIFFIN FRIESE (4044885)
> 7 TIFFIN FRIESE (4044885)
> 8 FRANK KOLARZ (4044048)
> 9 ALEJANDRO SANCHEZ (4041452)
> 10 BRENT RAMOS (4045946)
> 11 BETTY EHMEN (4045474)
> 12 BRANDA REAVES (4045546)
> 13 JAMES BISHOP (4045820)
> 14 ELIZABETH BURKE (4045844)
> 15 MORRIS DEVORE (4045835)
> 16 JULIE RENSHAW (4044396)
> 17 WILLIAM THOMASON (4044207)
> 18 GEORGE FOBIA (4044664)
>
>
> Any help would be appreciated.
>
> Chuong Nguyen
>

On Thu, 10 Jan 2008 16:00:41 -0800, "Chuong Nguyen"
<(E-Mail Removed)> wrote:

> I need a marco that can be use to delete the name in a cell in the
>whole column and keep the number only (4042989)
>
> A
> 1 FRED ELMS (4042989)
> 2 CLIFFORD LEE (I-4042705)
> 3 HOANG HUYNH (4044099)
> 4 BRENT FAUGHT (4043945)
> 5 ELIZABETH LAND (4044665)
> 6 TIFFIN FRIESE (4044885)
> 7 TIFFIN FRIESE (4044885)
> 8 FRANK KOLARZ (4044048)
> 9 ALEJANDRO SANCHEZ (4041452)
> 10 BRENT RAMOS (4045946)
> 11 BETTY EHMEN (4045474)
> 12 BRANDA REAVES (4045546)
> 13 JAMES BISHOP (4045820)
> 14 ELIZABETH BURKE (4045844)
> 15 MORRIS DEVORE (4045835)
> 16 JULIE RENSHAW (4044396)
> 17 WILLIAM THOMASON (4044207)
> 18 GEORGE FOBIA (4044664)
>
>
>Any help would be appreciated.
>
>Chuong Nguyen
>

======================
Option Explicit
Sub foo()
Dim c As Range
For Each c In Selection
c.NumberFormat = "@"
c.Value = Mid(c.Value, InStr(1, c.Value, "("))
Next c
End Sub
===================================
--ron

thanks so much for helping

Chuong Nguyen

"Chip Pearson" <(E-Mail Removed)> wrote in message
news:eHbkBd%(E-Mail Removed)...
> Try some code like the following. Select the cells to change and then run
> the code.
>
> Sub AAA()
> Dim R As Range
> Dim Pos As Long
> Dim S As String
> Application.EnableEvents = False
> On Error GoTo ERRH:
> For Each R In Selection.Cells
> If R.HasFormula = False Then
> Pos = InStr(1, R.Text, "(", vbBinaryCompare)
> If Pos = 0 Then
> S = R.Text
> Else
> S = Mid(R.Text, Pos)
> '''''''''''''''''''''''''''''
> ' If you want to get rid of
> ' the parentheses, uncomment
> ' the next two lines. Note,
> ' though, that Excel will
> ' treat a number within ()
> ' as a negative number.
> ''''''''''''''''''''''''''''
> 'S = Replace(S, "(", vbNullString)
> 'S = Replace(S, ")", vbNullString)
> End If
> R.Value = S
> End If
> Next R
> ERRH:
> Application.EnableEvents = True
> End Sub
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting LLC
> www.cpearson.com
> (email on the web site)
>
> "Chuong Nguyen" <(E-Mail Removed)> wrote in message
> news:OZWMCU%(E-Mail Removed)...
>> I need a marco that can be use to delete the name in a cell in the
>> whole column and keep the number only (4042989)
>>
>> A
>> 1 FRED ELMS (4042989)
>> 2 CLIFFORD LEE (I-4042705)
>> 3 HOANG HUYNH (4044099)
>> 4 BRENT FAUGHT (4043945)
>> 5 ELIZABETH LAND (4044665)
>> 6 TIFFIN FRIESE (4044885)
>> 7 TIFFIN FRIESE (4044885)
>> 8 FRANK KOLARZ (4044048)
>> 9 ALEJANDRO SANCHEZ (4041452)
>> 10 BRENT RAMOS (4045946)
>> 11 BETTY EHMEN (4045474)
>> 12 BRANDA REAVES (4045546)
>> 13 JAMES BISHOP (4045820)
>> 14 ELIZABETH BURKE (4045844)
>> 15 MORRIS DEVORE (4045835)
>> 16 JULIE RENSHAW (4044396)
>> 17 WILLIAM THOMASON (4044207)
>> 18 GEORGE FOBIA (4044664)
>>
>>
>> Any help would be appreciated.
>>
>> Chuong Nguyen
>>
>
>