Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank


This might work

=IF(ISNUMBER(A3),"X","")&IF(ISTEXT(A3),"Y","")

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568883

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

Thanks Bob..but i didnt understand ur question.


"Bob Phillips" wrote in message
...
Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

ISNUMBER is not working because the value is alpha numeric. for example --
E456

Thanks
Gary

"Bob Phillips" wrote in message
...
Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

That is exactly the point I was making, it is not a number, not Y, so what
do you want the formula to return in this case?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
ISNUMBER is not working because the value is alpha numeric. for example --
E456

Thanks
Gary

"Bob Phillips" wrote in message
...
Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?








  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
should return X. if A3 has text then it should return Y

"Bob Phillips" wrote in message
...
That is exactly the point I was making, it is not a number, not Y, so what
do you want the formula to return in this case?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
ISNUMBER is not working because the value is alpha numeric. for
example --
E456

Thanks
Gary

"Bob Phillips" wrote in message
...
Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?










  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

Then all you need is

=IF(ISNUMBER(A3),"X","Y")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
should return X. if A3 has text then it should return Y

"Bob Phillips" wrote in message
...
That is exactly the point I was making, it is not a number, not Y, so

what
do you want the formula to return in this case?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
ISNUMBER is not working because the value is alpha numeric. for
example --
E456

Thanks
Gary

"Bob Phillips" wrote in message
...
Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?












  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 108
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank


Gary wrote:
all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
should return X. if A3 has text then it should return Y


Hi Gary

As far as Excel is concerned, E344 is text.

If your alpha numerics are always the same format (i.e the second
character is a number), you can try:

=IF(ISNUMBER(MID(A3,2,1)*1),"X","Y")

Regards

Steve

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

its not working bob....i want X if the value in A3 is like E344 or F678 etc.
ISNUMBER is not taking it as a number.

Thanks for the help.

"Bob Phillips" wrote in message
...
Then all you need is

=IF(ISNUMBER(A3),"X","Y")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
should return X. if A3 has text then it should return Y

"Bob Phillips" wrote in message
...
That is exactly the point I was making, it is not a number, not Y, so

what
do you want the formula to return in this case?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
ISNUMBER is not working because the value is alpha numeric. for
example --
E456

Thanks
Gary

"Bob Phillips" wrote in message
...
Keep it simple

=IF(ISNUMBER(A3),"X",IF(A3="Y",""))

what about if not a number and not Y?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gary" wrote in message
...
Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?
















  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

wow steve..i tried MID but dint do *1.

it worked...thanks a ton

"Scoops" wrote in message
oups.com...

Gary wrote:
all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
should return X. if A3 has text then it should return Y


Hi Gary

As far as Excel is concerned, E344 is text.

If your alpha numerics are always the same format (i.e the second
character is a number), you can try:

=IF(ISNUMBER(MID(A3,2,1)*1),"X","Y")

Regards

Steve



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 266
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

"Gary" skrev i en meddelelse
...
all i wanna do is. if A3 has an alpha numeric value(E344) then the cell
should return X. if A3 has text then it should return Y



Hi Gary

Maybe this one:

=IF(ISNUMBER(A3)+(A3=""),"",IF(MIN(LEN(SUBSTITUTE( A3,{0,1,2,3,4,5,6,7,8,9},"")))<LEN(A3),"X","Y"))


--
Best regards
Leo Heuser

Followup to newsgroup only please.


  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

Alpha Numeric = 1 Alphabet (A to Z) and 3 Numerical Digits.

Text = All text, no numbers


"Ron Rosenfeld" wrote in message
...
On Mon, 7 Aug 2006 01:04:03 -0600, "Gary" wrote:

Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?


Your request is a little vague for me.

What is your definition of alpha numeric?

In other words, is there a particular sequence of letters and numbers, or
is it
any sequence which contains both letters and numbers?

Also, do the letters have to be restricted to the set [A-Z] (capital
letters
only) or are there other possibilities?

What is your definition of TEXT?

In Excel, text can include the set of numbers, depending on how they have
been
entered. It can also include any non-alphabetic character. Your
expectations
may differ.
--ron



  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

Ron Rosenfeld wrote...
....
Assuming that when you write Text you mean only large or small letters
[A-Za-z], then, although it could be done using worksheet formulas, it's easier
using regular expressions.

....

Text could just mean no numerals, \D.

Then try this formula:

=IF(REGEX.COUNT(A1,"^[A-Z]\d{3}$")=1,"X",IF(REGEX.COUNT(A1,"[^A-Za-z]"),"","Y"))

....

You're restricting the alphanumeric test to upper case letters only.
Myself, I'd use

=IF(REGEX.COMP(A1,"^[A-Z]\d{3}$",0),"X",IF(REGEX.COMP(A1,"^\D+$"),"Y",""))

But this could be done without MOREFUNC.

=IF(AND(ABS(CODE(UPPER(A1)&" ")-77.5)<13,COUNT(-MID(A1,2,3))),"X",
IF(AND(A1<"",COUNT(-MID(A1,{1,2,3,4},1))=0),"Y",""))

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

On Mon, 7 Aug 2006 01:04:03 -0600, "Gary" wrote:

Ok.....this is what am trying to do.

If A3=alpha numeric,"X", if A3=text,"Y", Blank

any ideas?


Your request is a little vague for me.

What is your definition of alpha numeric?

In other words, is there a particular sequence of letters and numbers, or is it
any sequence which contains both letters and numbers?

Also, do the letters have to be restricted to the set [A-Z] (capital letters
only) or are there other possibilities?

What is your definition of TEXT?

In Excel, text can include the set of numbers, depending on how they have been
entered. It can also include any non-alphabetic character. Your expectations
may differ.
--ron


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

On Mon, 7 Aug 2006 05:50:24 -0600, "Gary" wrote:

Alpha Numeric = 1 Alphabet (A to Z) and 3 Numerical Digits.

Text = All text, no numbers



Assuming that when you write Text you mean only large or small letters
[A-Za-z], then, although it could be done using worksheet formulas, it's easier
using regular expressions.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr (This can be easily distributed with workbooks if that
is an issue).

Then try this formula:

=IF(REGEX.COUNT(A1,"^[A-Z]\d{3}$")=1,"X",IF(REGEX.COUNT(A1,"[^A-Za-z]"),"","Y"))

The first regular expression restricts the length of the entry to four
characters. If there may be leading or trailing spaces to be ignored, the
formula will need to be modified.


--ron
  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default If A3=alpha numeric,"X", if A3=text,"Y", Blank

On 7 Aug 2006 11:39:22 -0700, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
Assuming that when you write Text you mean only large or small letters
[A-Za-z], then, although it could be done using worksheet formulas, it's easier
using regular expressions.

...

Text could just mean no numerals, \D.

Then try this formula:

=IF(REGEX.COUNT(A1,"^[A-Z]\d{3}$")=1,"X",IF(REGEX.COUNT(A1,"[^A-Za-z]"),"","Y"))

...

You're restricting the alphanumeric test to upper case letters only.
Myself, I'd use

=IF(REGEX.COMP(A1,"^[A-Z]\d{3}$",0),"X",IF(REGEX.COMP(A1,"^\D+$"),"Y",""))

But this could be done without MOREFUNC.

=IF(AND(ABS(CODE(UPPER(A1)&" ")-77.5)<13,COUNT(-MID(A1,2,3))),"X",
IF(AND(A1<"",COUNT(-MID(A1,{1,2,3,4},1))=0),"Y",""))


Different ways to skin a cat :-). And had I reviewed the available commands
before posting, I would have used REGEX.COMP rather than REGEX.COUNT.

Based on the OP's response to me, though, I purposely restricted the initial
character to caps [A-Z], and the definition of text to what I posted in my
response [A-Za-z]. If his definition of text is "anything not a digit" rather
than everything in the set of capital and small letters, he should use the \D.

Thanks.


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Imported Data creates blank cells that aren't really blank JackieD Excel Worksheet Functions 14 February 23rd 06 01:57 AM
enter numbers in blank cells bill gras Excel Worksheet Functions 2 September 21st 05 01:17 PM
Counting blank and filled cells within a range. greg7468 Excel Discussion (Misc queries) 3 June 28th 05 10:41 PM
blank cells R.VENKATARAMAN Excel Discussion (Misc queries) 1 April 6th 05 10:25 AM
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? Steve Excel Worksheet Functions 0 January 30th 05 10:11 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"