Help with formula, IF(A1 = LEFT/RIGHT/MID etc.

[Joyner]

Hi, I can't figure this out. Below is what I have tried, I may be on the right track, or someone may tell me I'm not or there is an earier way.

What I want to do is look at cells in a column and if the entry meet the right criteria, return the cells value, if not return blank("").

The criteria I am testing for is a string of 13 characters - 4 numbers; 1 alpha character; and 8 numbers. Any numbers or text is okay.

This is what I have so far.

=IF(BG14=(AND(ISNUMBER(LEFT(BG14,4)+0),ISNUMBER(RIGHT(BG14,8)+0),LEN(BG14)=13,ISTEXT(MID(BG14,5,1)+0))),BG14,"")

Does this formula just need a little tweeking or do I need something else?

Thanks for any help...Dean

 

[markyc]

What is the purpose of determining if the cell contains a certain format? Usually when I come across these types of question they can be avoided when you look at the 'big picture' of what is trying to be accomplished overall...

take out the BG14=
=IF(AND(ISNUMBER(LEFT(BG14,4)+0),ISNUMBER(RIGHT(BG14,8)+0),LEN(BG14)=13,ISTEXT(MID(BG14,5,1)+0)),BG14,"")

 

[Joyner]

This particular data gets pulled in as raw data and I'm only interested in the rows that meet this criteria and it is not as simple as a sort or any other means I'm aware of. I think the formula is my best means.

 

[Scott Huish]

If you download and install the MOREFUNC add-in available here:
http://xcell05.free.fr/english/index.html

then perhaps:
=IF(REGEX.FIND(A1,"^\d{4}[a-zA-Z]\d{8}$"),A1,"")

 

[Joyner]

markyc, I tried your version and its not returning results that it should? Any ideas?

HOTPEPPER, that looks interesting. I am firewalled from getting it. I will try when I get home. One question - is the add-in something that every user would need to add or is it a one time add-in to my spreadsheet?

Thanks...

 

[Scott Huish]

The current version of MOREFUNC allows you to embed it in the workbook.
(Tools..Morefunc..Embed Morefunc in the workbook)

 

[markyc]

sorry...try the following

=IF(AND(ISNUMBER(LEFT(BG14,4)+0),ISNUMBER(RIGHT(BG14,8)+0),LEN(BG14)=13,ISTEXT(MID(BG14,5,1))),BG14,"")

when you are checking text dont put + 0 otherwise it will try to convert to a number.