| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Removing text
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. |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
Hi Dave
It works great!! Thanks very much for your help. regards Dave "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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
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 "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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
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. Syed Rizvi wrote: 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 "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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
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 On Apr 10, 3:46*pm, Syed Rizvi wrote: 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 "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:"&LE N(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- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
Dave / Pete
You guys are wonderful, thanks very much for the quick help. It worked..... "Pete_UK" wrote: 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 On Apr 10, 3:46 pm, Syed Rizvi wrote: 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 "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:"&L EN(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- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
Well, it was Dave's macro in the first place ...
Pete On Apr 10, 4:30*pm, Syed Rizvi wrote: Dave / Pete You guys are wonderful, thanks very much for the quick help. It worked...... |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
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. "Dave Peterson" wrote: 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing text
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 Ash007 wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing text characters | Excel Worksheet Functions | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
| Removing numbers from the beginning of a text string | Excel Worksheet Functions | |||
| Sort or Filter option? | Excel Worksheet Functions | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
