How to test if the end character of a string is within a user-definedlist?

T

T2net

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
 
P

Pete_UK

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
 
T

T2net

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







- Afficher le texte des messages précédents -
Click to expand...

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
 
P

Pete_UK

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
 
D

Don Guillett

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
[email protected]
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







- Afficher le texte des messages précédents -
Click to expand...

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
 
T

T2net

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
 
D

Don Guillett

try isnumERIC and post back

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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
 
T

T2net

try isnumERIC and post back

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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







- Show quoted text -
Click to expand...

Hi!

Tried...better: no compiling error! However, it does not take the "a"
character condition into account, ie Result column is 1,2, 56.
 
T

T2net

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

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
 
Top