Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within a user-definedlist?
Hello,
I have a list in column which may look like 1 2 3a 3b 3g 56 67a 67c I would like to keep only the rows which contains numbers or numbers + a character. Any idea? Thanks, T2net MS VB 6.3, Excel 2003, Win2000 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within auser-defined list?
I think you need to give some further examples showing some which
would fail your criteria, as it looks to me as if they would all pass. Pete On Feb 22, 11:22*am, T2net wrote: Hello, I have a list in column which may look like 1 2 3a 3b 3g 56 67a 67c I would like to keep only the rows which contains numbers or numbers + a character. Any idea? Thanks, T2net MS VB 6.3, Excel 2003, Win2000 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within auser-defined list?
On 22 fév, 12:57, Pete_UK wrote:
I think you need to give some further examples showing some which would fail your criteria, as it looks to me as if they would all pass. Pete On Feb 22, 11:22*am, T2net wrote: Hello, I have a list in column which may look like 1 2 3a 3b 3g 56 67a 67c I would like to keep only the rows which contains numbers or numbers + a character. Any idea? Thanks, T2net MS VB 6.3, Excel 2003, Win2000- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - Oops sorry I meant the "a" character. I want to keep the rows with numbers (like 1,2, 56) and those with "a" (3a, 67a) and not the others T2net |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within auser-defined list?
Assuming your data is in column A starting with A1, try this in B1:
=IF(OR(ISNUMBER(A1),RIGHT(A1,1)="a"),A1,"remove") then copy the formula down as required. If you want to remove the rows that do not meet the criteria, then apply autofilter to column B. Choose "remove" from the filter pull- down, highlight all visible rows and Edit | Delete Row. Then select All from the pull-down. You could then delete column B. Hope this helps. Pete On Feb 22, 12:13*pm, T2net wrote: On 22 fév, 12:57, Pete_UK wrote: I think you need to give some further examples showing some which would fail your criteria, as it looks to me as if they would all pass. Pete On Feb 22, 11:22*am, T2net wrote: Hello, I have a list in column which may look like 1 2 3a 3b 3g 56 67a 67c I would like to keep only the rows which contains numbers or numbers + a character. Any idea? Thanks, T2net MS VB 6.3, Excel 2003, Win2000- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - Oops sorry I meant the "a" character. I want to keep the rows with numbers (like 1,2, 56) and those with "a" (3a, 67a) and not the others T2net- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within auser-defined list?
Unfortunately this gives as a result only 1 and 2 according to the
list I provided. I made a mix between your answers. I ended up with this macro: Sub test_number_and_a() mc = 2 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 x = Cells(i, mc) If Right(x, 1) < "a" And Not (IsNumber(x)) Then Rows(i).Delete Next i End Sub Unfortunately, I have a compiling error on Not (IsNumber(x)) . Any idea? Thanks, T2net On 22 fév, 14:51, "Don Guillett" wrote: Given your example Sub leavenumberandA() mc = 1 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 x = Cells(i, mc) If Len(x) 1 And Right(x, 1) < "a" Then Rows(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "T2net" wrote in message ... On 22 fév, 12:57, Pete_UK wrote: I think you need to give some further examples showing some which would fail your criteria, as it looks to me as if they would all pass. Pete On Feb 22, 11:22 am, T2net wrote: Hello, I have a list in column which may look like 1 2 3a 3b 3g 56 67a 67c I would like to keep only the rows which contains numbers or numbers + a character. Any idea? Thanks, T2net MS VB 6.3, Excel 2003, Win2000- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - Oops sorry I meant the "a" character. I want to keep the rows with numbers (like 1,2, 56) and those with "a" (3a, 67a) and not the others T2net- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within a user-defined list?
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within auser-defined list?
On 26 Feb, 14:05, "Don Guillett" wrote:
try isnumERIC and post back -- Don Guillett Microsoft MVP Excel SalesAid Software "T2net" wrote in message ... Unfortunately this gives as a result only 1 and *2 according to the list I provided. I made a mix between your answers. I ended up with this macro: Sub test_number_and_a() mc = 2 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 * * x = Cells(i, mc) * * If Right(x, 1) < "a" And Not (IsNumber(x)) Then Rows(i).Delete Next i End Sub Unfortunately, I have a compiling error on Not (IsNumber(x)) . Any idea? Thanks, T2net On 22 fév, 14:51, "Don Guillett" wrote: Given your example Sub leavenumberandA() mc = 1 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 x = Cells(i, mc) If Len(x) 1 And Right(x, 1) < "a" Then Rows(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "T2net" wrote in message ... On 22 fév, 12:57, Pete_UK wrote: I think you need to give some further examples showing some which would fail your criteria, as it looks to me as if they would all pass. Pete On Feb 22, 11:22 am, T2net wrote: Hello, I have a list in column which may look like 1 2 3a 3b 3g 56 67a 67c I would like to keep only the rows which contains numbers or numbers + a character. Any idea? Thanks, T2net MS VB 6.3, Excel 2003, Win2000- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - Oops sorry I meant the "a" character. I want to keep the rows with numbers (like 1,2, 56) and those with "a" (3a, 67a) and not the others T2net- Masquer le texte des messages précédents - - Afficher le texte des messages précédents -- Hide quoted text - - Show quoted text - Hi! Tried...better: no compiling error! However, it does not take the "a" character condition into account, ie Result column is 1,2, 56. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to test if the end character of a string is within auser-defined list?
On 26 Feb, 14:31, T2net wrote:
On 26 Feb, 14:05, "Don Guillett" wrote: try isnumERIC and post back -- Don Guillett Microsoft MVP Excel SalesAid Software "T2net" wrote in message ... Unfortunately this gives as a result only 1 and *2 according to the list I provided. I made a mix between your answers. I ended up with this macro: Sub test_number_and_a() mc = 2 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 * * x = Cells(i, mc) * * If Right(x, 1) < "a" And Not (IsNumber(x)) Then Rows(i).Delete Next i End Sub Unfortunately, I have a compiling error on Not (IsNumber(x)) . Any idea? Thanks, T2net On 22 fév, 14:51, "Don Guillett" wrote: Given your example Sub leavenumberandA() mc = 1 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 x = Cells(i, mc) If Len(x) 1 And Right(x, 1) < "a" Then Rows(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "T2net" wrote in message .... On 22 fév, 12:57, Pete_UK wrote: I think you need to give some further examples showing some which would fail your criteria, as it looks to me as if they would all pass. Pete On Feb 22, 11:22 am, T2net wrote: Hello, I have a list in column which may look like 1 2 3a 3b 3g 56 67a 67c I would like to keep only the rows which contains numbers or numbers + a character. Any idea? Thanks, T2net MS VB 6.3, Excel 2003, Win2000- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - Oops sorry I meant the "a" character. I want to keep the rows with numbers (like 1,2, 56) and those with "a" (3a, 67a) and not the others T2net- Masquer le texte des messages précédents - - Afficher le texte des messages précédents -- Hide quoted text - - Show quoted text - Hi! Tried...better: no compiling error! However, it does not take the "a" character condition into account, ie Result column is 1,2, 56.- Hide quoted text - - Show quoted text - It seems some space characters could be on the right side of my column elements, that is why it seems to be not working. Thanks for all your very useful and pertinent inputs. Now, I just need to add a trim condition for this space character problem I guess! T2net |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test left character is a number? | Excel Discussion (Misc queries) | |||
logical test and concatenate(string) | Excel Discussion (Misc queries) | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
Test String | Excel Discussion (Misc queries) | |||
Test for Single Character That is in an Array | Excel Worksheet Functions |