Formula problem.

[Spraggs]

Could someone please put me right with the formula I've attempted below.

=IF((C4=1)LEFT(A4,4),IF(C4=2)LEFT(A4,8),IF(C4=3)LEFT(A4,12),IF(C4=4)LEFT(A4,16),"")

Thanks Jase

 

[JackDanIce]

Is this what you mean?

=IF(C4=1,LEFT(A4,C4*4),IF(C4=2,LEFT(A4,C4*4),IF(C4=3,LEFT(A4,C4*4),IF(C4=4,LEFT(A4,C4*4),""))))

 

[Arcticwarrio]

I think you want this:

=IF(C4=1,LEFT(A4,4),IF(C4=2,LEFT(A4,8),IF(C4=3,LEFT(A4,12),IF(C4=4,LEFT(A4,16),""))))

 

[Arcticwarrio]

just realised it does the same as what jack posted lol

 

[Spraggs]

Sorry guys can I try again.

Cell A3 example ...
1234-ABC-DEF-HIJ-AB123

Cell C3 can be either 1,2,3,4 or 5

If cell C3 is 1 then return "" in D3
If cell C3 is 2 then return 1234 in D3
If cell C3 is 3 then return 1234-ABC in D3
If cell C3 is 4 then return 1234-ABC-DEF in D3
If cell C3 is 5 then return 1234-ABC-DEF-HIJ in D3

Thanks Jase

 

[JackDanIce]

Is it C3 or C4?
Change to fit:

=LEFT(A3,(INDEX(C3,{1,2,3,4,5})-1)*4)

 

[Arcticwarrio]

=if(c4=1,"",if(c4=2,left(a4,4),if(c4=3,left(a4,8),if(c4=4,left(a4,12),if(c4=5,left(a4,16),"")))))

 

[Spraggs]

I've tried this but it returns exactly what's in column A.

If I have 1234-ABC-DEF in column A then I need 1234-ABC in D when 3 is in C.

 

[Arcticwarrio]

You need to subtract 4 from jacks formula

ie


=LEFT(A3,(INDEX((C3,{1,2,3,4,5})-1)*4)-4)</pre>

 

[JackDanIce]

With the last formula I post, I have the following:

A1: 1234-ABC-DEF-HIJ-AB123
C3: 3

A2 has the formula:

=LEFT(A1,(INDEX(C3,{1,2,3,4,5})-1)*4)

And my screen is showing me in cell A2, as output:
1234-ABC

You need to change the cell references to what you require, I can't see your sheet (because I'm not pyschic) so a little bit of tinkering on your part, may be required.