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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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

ADVERTISEMENT

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,216
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,216
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,"")

.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,272
Messages
5,657,776
Members
418,413
Latest member
Radoslaw Poprawski

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
Top