Number or Text (letter)


A

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.
 
Ad

Advertisements

R

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
 
A

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.
 
A

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.
 
A

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!
 
Ad

Advertisements

A

alish

Ron, any help? Thanks.

alish said:
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!
 
G

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
 
P

Pete_UK

I think Gord meant to say:

You are NOT dealing with some helpdesk person.

Pete
 
R

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
 
R

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
 
Ad

Advertisements

K

Ken Wright

LOL

I think Gord meant to say:

You are NOT dealing with some helpdesk person.

Pete

<snip>
 
A

alish

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

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
 
R

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
 
Ad

Advertisements

R

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
 
G

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.
 
Ad

Advertisements

R

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top