Combining 3 formulas into one

[clickclick]

Hi there,

I'm trying to combine the 3 formulas below that I currently have in column F of the attached spreadsheet so that they all evaluate at the same time.

=VALUE(LEFT(C4,6))
=VALUE(MID(C6,3,10))
=VALUE(MID(C7,3,6))

Any help would be greatly appreciated.

Attachment 231528

[Pete_UK]

Put this formula in F2:

=IF(ISNUMBER(C2),VALUE(LEFT(C2,6)),IF(OR(ISNUMBER(SEARCH("-",C2)),LEN(C2)<14),VALUE(MID(C2,3,10)),VALUE(MID(C2,3,6))))

then copy down.

Hope this helps.

Pete

[clickclick]

Awesome, thanks so much for your help!

[Lemice]

Please disregard my following post. I'm glad that you've found a solution to your problem.

Hello,

Do you have any condition that indicate in which case take how many number (For example, on worksheet Bank, C7, you take out 6 numbers starting from the 3rd, but on other cases when there are letters, you take 10)

By the way, this file looks awfully familiar ...

[clickclick]

oh right...I didn't even think of that. Thanks for pointing it out.

DD9301795226-04-APR-13 if the text contains DD then =VALUE(MID(C6,3,10))
CB164319100325110740 if the text contains CB then =VALUE(MID(C7,3,6))
RD9300063626 if the text contains RD then =VALUE(MID(C9,3,10))
otherwise - =VALUE(LEFT(C2,6))

[clickclick]

i think that will work though right?

[Lemice]

If so, this might be the formula you are looking for

Please Login or Register  to view this content.

Assuming the indicating texts (DD, RD and CB) can only be the first 2. Let me know if they can be anywhere among the numbers ...

[clickclick]

Perfect, thanks again for your help, i'm sure this won't be the last time you will here from me. :-)