cell contents ending with a digit

[raventroth]

Hi

Is there a way of finding cell contents in a particular column that end with a digit rather than a letter?

i have 30000 cells to go through so would rather not to have to eyeball every one!!!!

many thanks

 

[mikerickson]

One way would be to use helper column with a formula like
=ISNUMBER(RIGHT(A1,1)+0)
and then AutoFilter that column to find the TRUE rows.

 

[stilesbn]

If your data is in column A then in column B you can put the formula

 =IF(ISNUMBER(RIGHT(A1,1)), 1,0)

Drag that all the way down. If it is a number it will return a one, else a 0. You can then do a count formula to see how many there are or sort them or do whatever you want.

 

[T. Valko]

Hi

Is there a way of finding cell contents in a particular column that end with a digit rather than a letter?

i have 30000 cells to go through so would rather not to have to eyeball every one!!!!

many thanks

Try this...

=COUNT(-RIGHT(A2))

A result of 1 means there's a number, 0 means there's no number.

 

[raventroth]

thanks for that guys but unfortunately i am just returning a column of zeros when there are clearly cells ending with a number. Dont know if it makes a difference but the cell contents are a mixture of numbers and letters, not just a cell of numbers and a cell of letters.
e.g deputy1
deputy

Sorry if my explanations are dreadful!!!

 

[JackDanIce]

This code will work but you need to change it to whatever column of data you're using:

Sub IncestousTurtles()
Dim i As Long, j As Long
Application.ScreenUpdating = False
With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
End With
i = Range("A" & Rows.Count).End(xlUp).row
j = Cells(1, Columns.Count).End(xlToLeft).Column + 1
With Cells(2, j)
    .Formula = "=RIGHT(A2,1)+0"
    .AutoFill Destination:=Range(Cells(2, j), Cells(i, j))
End With
With Range("A1", Cells(i, j))
    .AutoFilter
    .AutoFilter Field:=j, Criteria1:="<>#VALUE!", Operator:=xlAnd
End With
Application.ScreenUpdating = True
End Sub

Adjust the red A to whatever column you want to test

 

[T. Valko]

If your data is in column A then in column B you can put the formula

 =IF(ISNUMBER(RIGHT(A1,1)), 1,0)

Drag that all the way down. If it is a number it will return a one, else a 0. You can then do a count formula to see how many there are or sort them or do whatever you want.

The RIGHT function is a TEXT function so it returns a data type of TEXT.

Text 1 and numeric 1 are totally different values to Excel even though they look alike.

So, you have to convert the result of the RIGHT function to be a numeric value for ISNUMBER to work.

One way to do that is to use the double unary minus --.

=IF(ISNUMBER(--RIGHT(A1,1)),1,0)

 

[raventroth]

thanks valko, that worked!!

many thanks to everyone else!!

 

[T. Valko]

thanks valko, that worked!!

many thanks to everyone else!!

You're welcome. Thanks for the feedback!