Extraction from Cells

cmodica

New Member
Joined
Aug 9, 2010
Messages
2
I have a information in a cell the has text and a date. I want to extract the date from that cell and put it into its own cell, leaving the text in the current cell. I would like a simple way to do this without having to do it by hand. I would like to avoid the use of macro's.

Check 5/16/2008
Check 5/19/2008
Deposit 6/28/2008
Check 6/30/2008
This is how the current cell looks, I want to separate the date.
Thanks for the help.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Mr_Roscoe

Board Regular
Joined
Mar 28, 2006
Messages
200
cmodica - this should be a simple Text to Columns task.

Just highlight the whole column, from the toolbar go to Data -> Text to Columns, Step 1 you need to click the button for delimited then click Next, Step 2 make sure the Space button is ticked then click Next and make any selections on Step 3 (maybe none?) and click Finish.

trust this helps, Ian R.
 

cmodica

New Member
Joined
Aug 9, 2010
Messages
2
That would work for the information I put in my question. What I didn't realize is there is other text in the cell, here is a different example and some of the cells don't have dates in it:

Check 10/29/2008
Credit Card Charge 4/26/2009
Bill 9/24/2009
Bill 10/9/2009
Total Advertising and Promotion
Automobile Expense
Check 5/16/2008
Check 5/19/2008

Thanks for the help.
 

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
way too long but here it is. hope someone comes up with a shorter formula. assumes data sits in col A, and also that "/" is not used anywhere besides denoting dates.

=IF(ISNUMBER(FIND("/",A1)),RIGHT(A1,LEN(A1)-FIND("/",A1)+(MID(A1,FIND("/",A1)-2,1)=" ")+(MID(A1,FIND("/",A1)-3,1)=" ")*2+1),"Date Not Found")

copy down

P.S. thinking about it - could be shortened a little.

here:

=IF(ISNUMBER(FIND("/",A1)),TRIM(RIGHT(A1,LEN(A1)-FIND("/",A1)+3)),"Date Not Found")
 
Last edited:

Mr_Roscoe

Board Regular
Joined
Mar 28, 2006
Messages
200
only slightly shorter but another approach:

=MID(A1,FIND(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")),1),A1),10)

this assumes that there will be no text following the date.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
This may work for extracting the words to one column and the date to a second column, where the second column is formatted with Date number formatting:
Excel Workbook
ABC
1OriginalWord ExtractSerial Number Extract
2Check 5/16/2008Check5/16/2008
3Check 5/19/2008Check5/19/2008
4Deposit 6/28/2008Deposit6/28/2008
5Check 6/30/2008Check6/30/2008
6Check 10/29/2008Check10/29/2008
7Credit Card Charge 4/26/2009Credit Card Charge4/26/2009
8Bill 9/24/2009Bill9/24/2009
9Bill 10/9/2009Bill10/9/2009
10Total Advertising and PromotionTotal Advertising and Promotion.
11Automobile ExpenseAutomobile Expense.
12Check 5/16/2008Check5/16/2008
13Check 5/19/2008Check5/19/2008
...

Put this formula in cell B2 and copy down:


=IF(ISERROR(SEARCH("/",A2)),A2,TRIM(LEFT(A2,SEARCH("/",A2)-3)))


Put this formula in cell C2 and copy down:


=IF(ISERROR(SEARCH("/",A2)),"",TRIM(REPLACE(A2,1,SEARCH("/",A2)-3,""))+0)
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
If you have Excel 2007 or 2010, then this might work:

For words in Cell B2 and copy down:

=IFERROR(TRIM(LEFT(A2,SEARCH("/",A2)-3)),A2)


For dates in Cell C2 and copy down:

=IFERROR(TRIM(REPLACE(A2,1,SEARCH("/",A2)-3,""))+0,"")

.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,009
Messages
5,508,766
Members
408,692
Latest member
OptimalKR

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top