Hi,
i have a data in three different format in CELL"A2/A3/A4
i want to extract them in 2 column AS CELL B/2,B3,B4 & C2/C3/C4
the sample sheet is attached here with.
Please help
Hi,
i have a data in three different format in CELL"A2/A3/A4
i want to extract them in 2 column AS CELL B/2,B3,B4 & C2/C3/C4
the sample sheet is attached here with.
Please help
My English is very poor, so please be patient >_<"
Thanks & Regards.
hkbhansali
B2:
=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),100,100))
C2:
=DATEVALUE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),200,100)))
and drag both formulas down
You'll need to format column C as date.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
in B2
=LEFT(SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)),""),FIND(" ",SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)),"")))
in C2
=VALUE(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),9))
format as Date
A B C 1DATA RESULT RESULT 2FUTIDX NIFTY 26JUN2014 NIFTY 26-Jun-14 3FUTIDX BANKNIFTY 26JUN2014 BANKNIFTY 26-Jun-14 4OPTIDX NIFTY 26JUN2014 2700.00 CE NIFTY 26-Jun-14
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi hkbhansali
See if the attached works for you. I have formulas in columns B and C that do what I think you want.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
hi all,
thanks for prompt help,
Thank you all,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks