Split string using Excel

Question

I have a string with the following format:

StockCode Country Date Price equity

for example:

• 1 hk 10/31/12 C70.5 equity
• 101 hk 11/21/13 P63 equity
• 388 hk 10/17/12 P100 equity

I can extract Date by this Excel Command:

LEFT(RIGHT(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)),
LEN(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))-FIND(" ",
RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))),FIND(" ",
RIGHT(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)),
LEN(RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1)))-FIND(" ",
RIGHT(LEFT(A1,LEN(A1)-7),LEN(LEFT(A1,LEN(A1)-7))-FIND(" ",A1))))))

(line breaks for readbility)

result: 10/31/12

Anyone has a better solution for this?

here are some of my codes:

• how to get "C" and "P":

  TRIM(MID(A1,IFERROR(FIND("P",A1),FIND("C",A1)),1))
  

• how to get the strike price:

  TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-IFERROR(FIND("P",A1),
  FIND("C",A1))),"equity",""))
  

Answer 1

Try this, I think it should work for all dates with a 2 character year:

=TRIM(MID(A1,FIND("/",A1)-2,8))