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

Joyner

Well-known Member
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

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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,"")

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.

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

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

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.

Replies
1
Views
109
Replies
4
Views
367
Replies
5
Views
691
Replies
5
Views
282
Replies
0
Views
479

1,219,807
Messages
6,150,350
Members
450,952
Latest member
Zung

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back