Combining formula

[Wendy]

Hi

I have a column with an account number in it which I have to turn into a
specific format, for the mainframe to read it. I have the formula for the 8
character records but I have found some 11 character records which also have
to be handled. I wanted to do this in one formula but I can't get it to
work.

These are my 2 formula's

8 characters:-

=IF(LEN(IF(LEFT(B1,1)="F",MID(B1,2,7),B1))=8,"OLD"&B1,"OLD"&0&MID(B1,2,7))

11 Characters:-

=IF(LEN(IF(LEN(IF(LEFT(B47,1)="F",MID(B47,2,7),B47))=8,"OLD"&B47,"OLD"&0&MID
(B47,2,7)))=11,"OLD"&MID(B47,5,7),"OLD"&MID(B47,2,7))

Is this possible? Or is there a better way?

Thanks

Wendy

 

[Bob Phillips]

Is this what you mean?

=IF(LEFT(B1,1)="F","OLD"&IF(LEN(B1)=12,MID(B1,5,7),0&RIGHT(B1,LEN(B1)-1)),B1
)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

I haven't had my coffee yet, but maybe this?

=IF(AND(LEFT(B1,1)="F",OR(LEN(B1)=8,LEN(B1)=11)),"OLD"&RIGHT(B1,7),"OLD0"&RIGHT(B1,7))

Vaya con Dios,
Chuck, CABGx3

 

[Wendy]

Hi Guys,

Thanks, both formulas nearly work, I forgot on my original post to mention I
had already catered for 7 character fields and those with characters after
the account number, which was why I used the mid function.

Here is some sample data, if that helps to show what I'm being given, and
what is required.

Sample Data Desired Output
1436742, OLD01436742
F1929945 OLD01929945
JWF1080843 OLD01080843
F1283309/P OLD01283309
F1087060KW OLD01087060
JW F1289983 OLD01289983
F428200881 OLD04282008

Thanks

Wendy

 

[Guest]

Maybe this?

=IF(LEFT(A1,1)="F","OLD"&MID(A1,2,7),"OLD0"&MID(A1,FIND(1,A1,1),7))

Vaya con Dios,
Chuck, CABGx3

 

[Wendy]

Thanks Chuck, that works great.

Wendy

Maybe this?

=IF(LEFT(A1,1)="F","OLD"&MID(A1,2,7),"OLD0"&MID(A1,FIND(1,A1,1),7))

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Happy to help........thanks for the feedback...........

Vaya con dios,
Chuck, CABGx3