+ Reply to Thread
Results 1 to 5 of 5

Extract Text in 2 cell

  1. #1
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Extract Text in 2 cell

    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
    Attached Files Attached Files
    My English is very poor, so please be patient >_<"

    Thanks & Regards.
    hkbhansali

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Extract Text in 2 cell

    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!

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract Text in 2 cell

    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
    1
    DATA RESULT RESULT
    2
    FUTIDX NIFTY 26JUN2014 NIFTY
    26-Jun-14
    3
    FUTIDX BANKNIFTY 26JUN2014 BANKNIFTY
    26-Jun-14
    4
    OPTIDX 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

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Extract Text in 2 cell

    Hi hkbhansali

    See if the attached works for you. I have formulas in columns B and C that do what I think you want.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Extract Text in 2 cell

    hi all,
    thanks for prompt help,

    Thank you all,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. extract text from cell
    By L.LEE in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2013, 11:59 PM
  2. [SOLVED] Extract/Copy Text from Cell, and insert that text in a new row below
    By jamesoneill in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-07-2013, 09:06 AM
  3. Extract text from a cell
    By redrasta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2013, 12:33 PM
  4. Replies: 12
    Last Post: 09-15-2012, 10:20 PM
  5. Replies: 3
    Last Post: 07-25-2012, 07:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1