Nope. That line looks ok to me.

Maybe there's something else (some invisible character????). I'd delete it and
retype it.

Ash007 wrote:

Hi Dave,
I am trying to use this macro but it breaks/stops and highlights 'syntax
error' at line:

If FirstNumberPos 0 Then
any idea why it would do that. please help

thanks.

"Dave Peterson" wrote:

One way with a macro:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim FirstNumberPos As Long

Set myRng = Selection

For Each myCell In myRng.Cells
With myCell
FirstNumberPos = 0
For iCtr = 1 To Len(.Value)
If IsNumeric(Mid(.Value, iCtr, 1)) Then
FirstNumberPos = iCtr
Exit For
End If
Next iCtr
If FirstNumberPos 0 Then
.Value = Trim(Left(.Value, FirstNumberPos - 1))
End If
End With
Next myCell

End Sub

Just select your range and run the macro. Because it updates in place, make
sure you test it against a copy of your data (or close without saving).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dave wrote:

Hi Biff
Thanks for your help which is very much appreciated.
At some point there will be names without any numbers so could you please
put a error check in the formula that you posted.
Is there a macro that could do a similar job instead of a long formula.
regards
Dave

"Biff" wrote:

Hi!

Here's one way assuming all entries have some numerical digits in them:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=LEFT(A1,MATCH(1,(CODE(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1))47)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN (A1))),1))<58),0)-2)

If there may be entries WITHOUT numbers in them this formula will return an
error. I can include an error trap for this but it will make the formula
twice as long. Post back if that is the case.

Biff

"Dave" wrote in message
...
Is there anyway i can remove unwanted text and numbers from samples below
Almuraad(IRE) 11
Blythe Knight(IRE) 34 Course and Dist winner
Ecomium(IRE) 174 Distance winnerBeaten Favourite
Echo of Light 25
Khyber Kim 36
and just leave the names ie: Almuraad(IRE), Khyber Kim etc.
Any help will be welcomed.




--

Dave Peterson


--

Dave Peterson