Removing text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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:"&LEN(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
 
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
 
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
 
I tried this macro but it removed all the numbers instead of the text in the
column. Is there a way of removing just text and leaving numbers behind. Some
numbers starts with zero as well.
Any help would be appreciated, thanks
Syed
 
Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myStr As String

Set myRng = Selection

For Each myCell In myRng.Cells
myStr = ""
With myCell
For iCtr = 1 To Len(.Value)
If IsNumeric(Mid(.Value, iCtr, 1)) Then
myStr = myStr & Mid(.Value, iCtr, 1)
End If
Next iCtr
.numberformat = "@" 'you want the leading 0's kept???
.Value = myStr
End With
Next myCell

End Sub

If this doesn't help, post some typical before values and what you want to see
after.
 
The macro was designed to extract the first part (text) using LEFT. In
your case you want the RIGHT part, so change this line:

.Value = Trim(Left(.Value, FirstNumberPos - 1))

near the end to this:

.Value = "" & Right(.Value, Len(.Value) -
FirstNumberPos + 1)

This will preserve any leading zeroes.

Hope this helps.

Pete
 
Dave / Pete
You guys are wonderful, thanks very much for the quick help. It worked.....
 
Hi Dave,
I am trying to use this macro but it breaks/stops and highlights 'syntax
error' at line:
If FirstNumberPos > 0 Then
Click to expand...
any idea why it would do that. please help

thanks.
 
Nope. That line looks ok to me.

Maybe there's something else (some invisible character????). I'd delete it and
retype it.
 
You are right Dave. It works now at home pc just fine. something wrong with
my work pc. Thanks heaps for posting this.

I will try my luck the next question
- how can i get rid of all characers from a cell and jsut leave numbers.
Characters are at the beginning and at the end of the cell.
 
And put it in the same cell?

You could select the range to fix and then run a macro like:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myStr As String

Set myRng = Selection

For Each myCell In myRng.Cells
myStr = myCell.Value
For iCtr = 1 To Len(myStr)
If IsNumeric(Mid(myStr, iCtr, 1)) Then
'perfect, don't touch it
Else
Mid(myStr, iCtr, 1) = " "
End If
Next iCtr
myStr = Replace(myStr, " ", "")
myCell.Value = myStr
Next myCell

End Sub


This will change:
abcd1234efgh56
to
123456

and
1234.56
to
123456


You are right Dave. It works now at home pc just fine. something wrong with
my work pc. Thanks heaps for posting this.

I will try my luck the next question
- how can i get rid of all characers from a cell and jsut leave numbers.
Characters are at the beginning and at the end of the cell.
Click to expand...
 
Back
Top