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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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,"")

.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top