Delete text in a string marco help

C

Chuong Nguyen

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
 
D

Dave Peterson

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.
 
C

Chip Pearson

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)
 
R

Ron Rosenfeld

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

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

Chuong Nguyen

thanks so much for helping

Chuong Nguyen

Chip Pearson said:
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)
Click to expand...
 
Top