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

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

markyc

Board Regular
Joined
Aug 23, 2006
Messages
149
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

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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.
 

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The current version of MOREFUNC allows you to embed it in the workbook.
(Tools..Morefunc..Embed Morefunc in the workbook)
 

markyc

Board Regular
Joined
Aug 23, 2006
Messages
149
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.
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top