Number or Text (letter)

[alish]

Hi everyone. I am stuck with the delemma here (below):

column1 Column2 Formula
123, 231aa, 234ff TRUE ?
123aa, 234bb, 345cc FALSE ?
123aa, 234bb, 345cc, 456dd, 567ee FALSE ?
987ff, 876, 765, 654ee, 432pp TRUE ?

Basically, how do I get the results in Column2? In Column1 i have
alpha-numeric data and Column2 should have a formula to tell me whether it is
TRUE (if the entire data in the cell there is at least one text with no
letter at the end or beginning) and FALSE if the cell has the data of texts
that have letter at the end of beginning of each text. I appreciate your help
in advance. Thanks.

 

[alish]

Any one? Please help... Thanks.

 

[Ron Rosenfeld]

Hi everyone. I am stuck with the delemma here (below):

column1 Column2 Formula
123, 231aa, 234ff TRUE ?
123aa, 234bb, 345cc FALSE ?
123aa, 234bb, 345cc, 456dd, 567ee FALSE ?
987ff, 876, 765, 654ee, 432pp TRUE ?

Basically, how do I get the results in Column2? In Column1 i have
alpha-numeric data and Column2 should have a formula to tell me whether it is
TRUE (if the entire data in the cell there is at least one text with no
letter at the end or beginning) and FALSE if the cell has the data of texts
that have letter at the end of beginning of each text. I appreciate your help
in advance. Thanks.

Your examples and your question are not quite the same, leading me to some
confusion.

From your examples, it seems you want to return TRUE if any of the comma
separated entries in the string are all numbers.

But from your question, you would also want TRUE returned if the first and last
character was number, even if there was text in the middle. So that 9abc2
would return TRUE.

Either can be easily done with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like
=TestEntry(A1)
in some cell.

To test if any substring is ALL numbers:
==========================================
Option Explicit
Function TestEntry(s As String) As Boolean
Dim Entries
Dim i As Long
TestEntry = False
Entries = Split(s, ",")
For i = 0 To UBound(Entries)
If IsNumeric(Entries(i)) Then
TestEntry = True
Exit Function
End If
Next i
End Function
==========================================

To test if only first and last characters are numbers:

=TestEntry2(A1)
============================================
Function TestEntry2(s As String) As Boolean
Dim Entries
Dim i As Long
TestEntry2 = False
Entries = Split(s, ",")
For i = 0 To UBound(Entries)
If Trim(Entries(i)) Like "#*#" Then
TestEntry2 = True
Exit Function
End If
Next i
End Function
====================================
--ron

 

[alish]

Or, at least, if you could tell me what formula to use that identifies how
many letters and how many numbers in the value cell i can figure it out
myself. Thanks again.

 

[alish]

Ron, really appreciate your response. But i want TRUE if there is only
numbers and no letters (at all) in the text. Thanks again, Sir.

 

[alish]

Ron, is there a way to make TRUE if the lastcharacters are "FF", besides that
all the numbers are TRUE as well? Only the two conditions must return TRUE:
all numbers and ###FF. Thanks again!

 

[alish]

Ron, any help? Thanks.

Ron, is there a way to make TRUE if the lastcharacters are "FF", besides that
all the numbers are TRUE as well? Only the two conditions must return TRUE:
all numbers and ###FF. Thanks again!

 

[Gord Dibben]

Have a little patience alish.

You are dealing with some helpdesk person.

Ron does not do this for a living and may be out for a round of golf.

I'm sure he will get back to you in time.


Gord Dibben MS Excel MVP

 

[Pete_UK]

I think Gord meant to say:

You are NOT dealing with some helpdesk person.

Pete

 

[Ron Rosenfeld]

Ron, is there a way to make TRUE if the lastcharacters are "FF", besides that
all the numbers are TRUE as well? Only the two conditions must return TRUE:
all numbers and ###FF. Thanks again!

===========================
Option Explicit
Function TestEntry(s As String) As Boolean
Dim Entries
Dim i As Long
TestEntry = False
Entries = Split(s, ",")
For i = 0 To UBound(Entries)
If IsNumeric(Entries(i)) Or _
(IsNumeric(Left(Entries(i), Len(Entries(i)) - 2)) _
And Right(Entries(i), 2) = "FF") Then
TestEntry = True
Exit Function
End If
Next i
End Function
=================================

will do it. But if you are going to add multiple conditions, I would rewrite
this using Regular Expressions, which would be more flexible.
--ron

 

[Ron Rosenfeld]

Ron, is there a way to make TRUE if the lastcharacters are "FF", besides that
all the numbers are TRUE as well? Only the two conditions must return TRUE:
all numbers and ###FF. Thanks again!

In my previous post, I mentioned using Regular Expressions if your list of
possible conditions becomes more involved than just the two you mentioned.

For the record, here is an example using the two conditions you have, so far,
wanted to return as true:

========================
Function TestEntryRE(s As String) As Boolean
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d+(FF)?\b"
re.ignorecase = False
TestEntryRE = re.Test(s)
End Function
=========================

Line 5 is not really needed, as it is the default case, so this could be a bit
shorter:

============================
Function TestEntryRE(s As String) As Boolean
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d+(FF)?\b"
TestEntryRE = re.Test(s)
End Function
===============================

Adding more conditions would be a matter of simply modifying the re.Pattern


--ron

 

[Ken Wright]

LOL

I think Gord meant to say:

You are NOT dealing with some helpdesk person.

Pete

<snip>

 

[Gord Dibben]

That's what Gord meant to say<g>

Thanks Pete

I think Gord meant to say:

You are NOT dealing with some helpdesk person.

Pete

 

[alish]

Thank you, guys. Sorry for the inconvenience i may have caused. Sorry Ron and
really appreciate your help, man.

 

[Gladiator]

Ron, yesterday i did the changes in the codes you sen by adding a new Elseif
statement according to the new criteria and it worked. What do you think:

Function TestEntry(s As String) As Boolean
Dim Entries
Dim i As Long
TestEntry = False
Entries = Split(s, ",")
For i = 0 To UBound(Entries)
If IsNumeric(Entries(i)) Then
TestEntry = True
ElseIf Trim(Entries(i)) Like "#*FF" Then
TestEntry = True
Exit Function
End If
Next i
End Function

 

[Ron Rosenfeld]

Thank you, guys. Sorry for the inconvenience i may have caused. Sorry Ron and
really appreciate your help, man.

No inconvenience. We do this because we want to. Glad to help. Thanks for
the feedback.
--ron

 

[Ron Rosenfeld]

Ron, yesterday i did the changes in the codes you sen by adding a new Elseif
statement according to the new criteria and it worked. What do you think:

Function TestEntry(s As String) As Boolean
Dim Entries
Dim i As Long
TestEntry = False
Entries = Split(s, ",")
For i = 0 To UBound(Entries)
If IsNumeric(Entries(i)) Then
TestEntry = True
ElseIf Trim(Entries(i)) Like "#*FF" Then
TestEntry = True
Exit Function
End If
Next i
End Function

The problem with your code is that the '*' does not modify the '#' but rather
stands alone.

So your code will match, as TRUE, ANY string that starts with a number and ends
with FF. e.g. 98ABFF.

The OP wrote:

"all numbers and ###FF"

If we could be certain that all of the valid strings that ended with 'FF' had
exactly three digits, then you could use this line:

ElseIf Trim(Entries(i)) Like "###FF" Then

But it's not entirely clear what a full set of specifications for the problem
would look like, so I coded for an unlimited number of digits followed by FF.

--ron

 

[Gladiator]

Ron, thanks for the clarification. This is intended for the MS Project data.
It produces the data with numbers and letters like "FF" - finish to finish,
"SS" -start to sart, "FS" - finish to start and etc. where the letters always
come at the end. So i wanted to select the rows that contains only the ones
with FF and numbers only. I should have explained it to you earlier but
thought it would complicate the issue. But I highly appreciate your help with
this, brother.

 

[Ron Rosenfeld]

Ron, thanks for the clarification. This is intended for the MS Project data.
It produces the data with numbers and letters like "FF" - finish to finish,
"SS" -start to sart, "FS" - finish to start and etc. where the letters always
come at the end. So i wanted to select the rows that contains only the ones
with FF and numbers only. I should have explained it to you earlier but
thought it would complicate the issue. But I highly appreciate your help with
this, brother.

A small change to my previously posted Regular Expression example would
accomplish that with less code:

============================
Function TestEntryRE(s As String) As Boolean
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\d+(FF|FS|SS)?\b"
TestEntryRE = re.Test(s)
End Function
===============================

That will return TRUE if the string contains digits only, or digits followed by
either FF, FS or SS

If you want to specify that the substring should contain exactly three digits,
then just change to:

re.Pattern = "\b\d{3}(FF|FS|SS)?\b"
--ron