MID LEFT RIGHT

[vmjan02]

I tried every think but no luck, any help please.

Book2
	A	B	C
1	InPut	OutPut 1	OutPut 2
2	130-00_RFAIRDASOL1/14/1/1	130-00_RFAIRDASOL1/14/1	130-00_RFAIRDASOL1/14
3	130-00_RFAIRDASOL1/16/1/1	130-00_RFAIRDASOL1/16/1	130-00_RFAIRDASOL1/16
4	130-00_RFAIRDASOL1/16/1/2	130-00_RFAIRDASOL1/16/1	130-00_RFAIRDASOL1/16
5	130-00_RFAIRDASOL1/1/1/2	130-00_RFAIRDASOL1/1/1	130-00_RFAIRDASOL1/1
6	130-00_RFAIRDASOL1/1/1/20	130-00_RFAIRDASOL1/1/1	130-00_RFAIRDASOL1/1
7	130-00_RFAIRDASOL1/1/1/21	130-00_RFAIRDASOL1/1/1	130-00_RFAIRDASOL1/1
8	130-00_RFAIRDASOL1/1/1/3	130-00_RFAIRDASOL1/1/1	130-00_RFAIRDASOL1/1
9	130-00_RFAIRDASOL1/1/1/30	130-00_RFAIRDASOL1/1/1	130-00_RFAIRDASOL1/1
10	130-00_RFAIRDASOL1/1/1/4	130-00_RFAIRDASOL1/1/1	130-00_RFAIRDASOL1/1
11	130-00_RFAIRDASOL1/1/1/5	130-00_RFAIRDASOL1/1/1	130-00_RFAIRDASOL1/1
12	114-00_MNARRD00OLA/8/3/65	114-00_MNARRD00OLA/8/3	114-00_MNARRD00OLA/8
13	114-00_MNARRD00OLA/8/3/66	114-00_MNARRD00OLA/8/3	114-00_MNARRD00OLA/8
14	114-00_MNARRD00OLA/8/4/1	114-00_MNARRD00OLA/8/4	114-00_MNARRD00OLA/8
15	114-00_MNARRD00OLA/8/4/17	114-00_MNARRD00OLA/8/4	114-00_MNARRD00OLA/8
16	114-00_MNARRD00OLA/8/4/65	114-00_MNARRD00OLA/8/4	114-00_MNARRD00OLA/8
17	114-00_MNARRD00OLA/8/4/66	114-00_MNARRD00OLA/8/4	114-00_MNARRD00OLA/8
18	296-00_JRNHKH01OL5/9/14/66	296-00_JRNHKH01OL5/9/14	296-00_JRNHKH01OL5/9
19	296-00_JRNHKH01OL5/9/14/67	296-00_JRNHKH01OL5/9/14	296-00_JRNHKH01OL5/9
20	296-00_JRNHKH01OL5/9/14/71	296-00_JRNHKH01OL5/9/14	296-00_JRNHKH01OL5/9
21	296-00_JRNHKH01OL5/9/14/80	296-00_JRNHKH01OL5/9/14	296-00_JRNHKH01OL5/9
22	296-00_JRNHKH01OL5/9/14/85	296-00_JRNHKH01OL5/9/14	296-00_JRNHKH01OL5/9
23	296-00_JRNHKH01OL5/9/14/90	296-00_JRNHKH01OL5/9/14	296-00_JRNHKH01OL5/9
24	417-00_QBLTDN00OL4/8/12/100	417-00_QBLTDN00OL4/8/12	417-00_QBLTDN00OL4/8
25	417-00_QBLTDN00OL4/8/12/101	417-00_QBLTDN00OL4/8/12	417-00_QBLTDN00OL4/8
26	417-00_QBLTDN00OL4/8/12/66	417-00_QBLTDN00OL4/8/12	417-00_QBLTDN00OL4/8
27	417-00_QBLTDN00OL4/8/12/67	417-00_QBLTDN00OL4/8/12	417-00_QBLTDN00OL4/8
28	417-00_QBLTDN00OL4/8/12/98	417-00_QBLTDN00OL4/8/12	417-00_QBLTDN00OL4/8
29	417-00_QBLTDN00OL4/8/13/67	417-00_QBLTDN00OL4/8/13	417-00_QBLTDN00OL4/8
30	417-00_QBLTDN00OL4/8/14/65	417-00_QBLTDN00OL4/8/14	417-00_QBLTDN00OL4/8
31	417-00_QBLTDN00OL4/8/15/100	417-00_QBLTDN00OL4/8/15/	417-00_QBLTDN00OL4/8
32	417-00_QBLTDN00OL4/8/15/97	417-00_QBLTDN00OL4/8/15	417-00_QBLTDN00OL4/8
33	417-00_QBLTDN00OL4/8/15/98	417-00_QBLTDN00OL4/8/15	417-00_QBLTDN00OL4/8
Sheet1

 

[RoryA]

For 365:

=TEXTBEFORE(A2,"/",3)

and

=TEXTBEFORE(A2,"/",2)

 

[Fluff]

How about for col B

=TEXTBEFORE(A2,"/",-1)

and for col C use -2 rather than -1

 

[vmjan02]

For 365:

=TEXTBEFORE(A2,"/",3)

and

=TEXTBEFORE(A2,"/",2)

afart from 365 will it work on 2016 or 2017...

 

[RoryA]

No (and there is no 2017). If you need it to work on specific versions, you should really specify that in the post in future.

 

[RoryA]

For 2016:

=LEFT($A2,FIND("|",SUBSTITUTE($A2,"/","|",3))-1)

and

=LEFT($A2,FIND("|",SUBSTITUTE($A2,"/","|",2))-1)

 

[vmjan02]

thanks a ton, sure will do that.

 

[MicCh]

=TEXTBEFORE(A2,"/",-1)

 

[Fluff]

Why post a formula that is exactly the same as one posted 5 hours earlier?