# Formula woes...

#### tlynn

##### Board Regular
=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)=47,47,"")))

I know that the "LEFT" function is text based, but it was a last ditch effort to get this to work.

Basically it starts by checking to see if c12 is filled or not...works fine. Then it goes on to see if c12 is different from c10 and if it is, is it filled with a 5 digit number starting with 47? If it is then the cell that this formula is nested in will, rather, should put a 47 in it and if there is not then the cell should remain blank or empty.

Any suggestions?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### jindon

##### MrExcel MVP
Hi

Have you tried

IF(C12="","",IF(C12<>C10,IF(And(Len(C12)=5,LEFT(C12,2)="47"),47,"")))

Left function returns string type result.

#### debooo

##### Board Regular
so if c10 said 47555 and c12 said 47111 the formula should return 47

but if either c10 and c12 is blank, or if either start with something other then 47, or if they are identical numbers the formula should reutn a blank?

#### tlynn

##### Board Regular
Jindon, no dice...if c10 and c12 are the same I get "FALSE"..if they are the same it should basically make the cell empty.

I think you are correct Debooo...

if c10 and c12 are the same the cell this formula is nested in should be blank. If c12 is different it should basically pop up a 47 in the cell.

#### tlynn

##### Board Regular
Maybe I am making this formula too difficult. If that is the case, any suggestion on a different way to do it would be appreciated...

#### Yogi Anand

##### MrExcel MVP
=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)=47,47,"")))

I know that the "LEFT" function is text based, but it was a last ditch effort to get this to work.

Basically it starts by checking to see if c12 is filled or not...works fine. Then it goes on to see if c12 is different from c10 and if it is, is it filled with a 5 digit number starting with 47? If it is then the cell that this formula is nested in will, rather, should put a 47 in it and if there is not then the cell should remain blank or empty.

Any suggestions?

Hi tlynn:

Code:
=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)="47",47,"")))
Does this work for you now?

#### debooo

##### Board Regular
=IF(C12="","",IF(C12<>C10,IF(AND(LEN(C12)=5,LEFT(C12,2)="47"),47," ")," "))

i barely tweeked the formula above, is this what you wanted?

#### tlynn

##### Board Regular
Again no dice...funny thing is that was one of the original ways I tried it. when C10 and c12 are the same I get a false statement...if both cells are the same the cell should remain blank. Any other suggestions?

#### tlynn

##### Board Regular
Debooo you are a rock star! Perfect! Thank you all for your help on this one.

#### Yogi Anand

##### MrExcel MVP
Hi tlynn:

Code:
=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)="47",47,""),""))

Replies
2
Views
293
Replies
3
Views
226
Replies
6
Views
131
Replies
0
Views
537
Replies
5
Views
193

1,191,274
Messages
5,985,698
Members
439,974
Latest member
sjoerdbosch

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

### Which adblocker are you using?

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