Delete text in a string marco help
 

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

Delete text in a string marco help
 

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

Delete text in a string marco help
 

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" wrote in message
...
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

Delete text in a string marco help
 

On Thu, 10 Jan 2008 16:00:41 -0800, "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


======================
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

Delete text in a string marco help
 

thanks so much for helping

Chuong Nguyen

"Chip Pearson" wrote in message
...
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" wrote in message
...
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