Text Function

[pmari]

Hi Friends,

I am using TEXT Function in the adjacent column to separate First 5 or 6 or 7 Letters with Numbers.

Is it possible to alter the formula (or VBA Code) depends upon 5th or 3rd digit. Sample given below.


F010CEA45 =LEFT(B2,5)
F010CEB44 =LEFT(B3,5)
F010DSB42 =LEFT(B4,5)
F010TCB11 =LEFT(B5,5)
F010SFP11 =LEFT(B6,6) 5th Letter is "S"
F010SFR00 =LEFT(B7,6) 5th Letter is "S"
F010SFR22 =LEFT(B8,6) 5th Letter is "S"
F020SFR55 =LEFT(B9,6) 5th Letter is "S"
F020SFP58 =LEFT(B10,6) 5th Letter is "S"
F030LC781 =LEFT(B11,7) 3rd Digit is "3"
F030LC792 =LEFT(B12,7) 3rd Digit is "3"
F010ZA701 =LEFT(B13,5)
F010ZT854 = LEFT(B14,5)


Please look into this.

Many thanks in advance.

[Sorin Sion]

Try this on a cell from the second row and then copy the formula to the last row of interest:

=IF(MID(B2,3,1)="3",LEFT(B2,7),IF(MID(B2,5,1)="S",LEFT(B2,6),LEFT(B2,5)))

From what you state, it's not clear what is the precedence between rules when (and if) you have in the same string both a "3" in the third position and an "S" in the 5th position.
Depending on what you would like the outcome to be, you might want to swap the first condition with the second.

[GTO]

pmari,

You already started a thread here at: http://vbaexpress.com/forum/showthread.php?t=39557

...at about 10:48 local time, where Sorin Sion has reponded with a possible solution.

Also cross-posted at http://www.mrexcel.com/forum/showthread.php?t=587706

Please read Here (http://www.excelguru.ca/node/7)

Mark

[pmari]

Dear GTO,

I admit that i posted the query in two forums with one day time interval.

I Read rules about cross posting, will be careful and not repeat the mistake.

Thanks for the correction.

[pmari]

Dear Sorin Sion,

Many thanks for your reply and it works well for my application.

And you are absolutely right, there is a possibility of "3" in the third position and an "S" in the 5th position. is such case i need to extract 8 digit instead of 7.

Is it possible to combine TWO IF Conditions, i will try to find a solution with your given guidance.

Thanks again.