Formula or Code to Separate Dates from String of Text?

C

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
496
Office Version
  1. 365
Platform
  1. Windows
Hello guys, hoping you guys could help me once more.

I will have a Data Set with 3 columns(A:C). I'm essentially trying to extract the numbers from Column C(Dates) and copy them to column D. Would that be possible?

Ideally the final results will look like this(Sorry, do not have XL2BB on this PC)

Invoice DatePosting MonthPhone ExpenseDates
3/2/2022​
3-2022​
03.22 Phone Bill
3.22​
3/2/2022​
3-2022​
04.22 Phone Bill
4.22​
3/2/2022​
3-2022​
05.22 Phone Bill
5.22​
3/2/2022​
3-2022​
06.22 Phone Bill
6.22​
3/2/2022​
3-2022​
07.22 Phone Bill
7.22​
3/2/2022​
3-2022​
08.22 Phone Bill
8.22​
3/2/2022​
3-2022​
09.22 Phone Bill
9.22​
Invoice DatePosting MonthRepairs Expense
3/2/2022​
3-2022​
03/02 Repairs
3/2​
3/2/2022​
3-2022​
03/07 Repairs
3/7​
3/2/2022​
3-2022​
03/09 Repairs
3/9​
3/2/2022​
3-2022​
03/02 Repairs
3/10​
Invoice DatePosting MonthInternet
3/2/2022​
3-2022​
01/05-02/04 Internet01-05-02/04
3/2/2022​
3-2022​
02/05-03/04 Internet02/05-03/04


I would like the dates on Column D(Any Date Format will suffice)

Thank you guys.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Try

Book1
ABCDEF
1Invoice DatePosting MonthPhone ExpenseDatesDates
23/2/20223-202203.22 Phone Bill3.223/22/2022
33/2/20223-202204.22 Phone Bill4.224/22/2022
43/2/20223-202205.22 Phone Bill5.225/22/2022
53/2/20223-202206.22 Phone Bill6.226/22/2022
63/2/20223-202207.22 Phone Bill7.227/22/2022
73/2/20223-202208.22 Phone Bill8.228/22/2022
83/2/20223-202209.22 Phone Bill9.229/22/2022
9
10
11Invoice DatePosting MonthRepairs Expense
123/2/20223-202203/02 Repairs3/23/2/2022
133/2/20223-202203/07 Repairs3/73/7/2022
143/2/20223-202203/09 Repairs3/93/9/2022
153/2/20223-202203/02 Repairs3/103/2/2022
16
17
18Invoice DatePosting MonthInternet
193/2/20223-202201/05-02/04 Internet01-05-02/0401/05-02/04
203/2/20223-202202/05-03/04 Internet02/05-03/0402/05-03/04
21
22
23
Sheet2
Cell Formulas
RangeFormula
E19:E20,E12:E15,E2:E8E2=IFERROR(SUBSTITUTE(LEFT(C2,FIND(" ",C2)-1),".","/")+0,SUBSTITUTE(LEFT(C2,FIND(" ",C2)-1),".","/"))
 
Upvote 0
Hi,

Another way:

NOTE: formula in D2 copied down as far as needed.

Book3.xlsx
ABCD
1Invoice DatePosting MonthPhone ExpenseDates
23/2/20223-202203.22 Phone Bill03.22
33/2/20223-202204.22 Phone Bill04.22
43/2/20223-202205.22 Phone Bill05.22
53/2/20223-202206.22 Phone Bill06.22
63/2/20223-202207.22 Phone Bill07.22
73/2/20223-202208.22 Phone Bill08.22
83/2/20223-202209.22 Phone Bill09.22
9 
10 
11Invoice DatePosting MonthRepairs Expense 
123/2/20223-202203/02 Repairs03/02
133/2/20223-202203/07 Repairs03/07
143/2/20223-202203/09 Repairs03/09
153/2/20223-202203/02 Repairs03/02
16 
17 
18Invoice DatePosting MonthInternet 
193/2/20223-202201/05-02/04 Internet01/05-02/04
203/2/20223-202202/05-03/04 Internet02/05-03/04
Sheet1052
Cell Formulas
RangeFormula
D2:D20D2=IF(ISERR(LEFT(C2)+0),"",LEFT(C2,FIND(" ",C2)-1))
 
Last edited:
Upvote 0
jtakw said:
Hi,

Another way:

Book3.xlsx
ABCD
1Invoice DatePosting MonthPhone ExpenseDates
23/2/20223-202203.22 Phone Bill03.22
33/2/20223-202204.22 Phone Bill04.22
43/2/20223-202205.22 Phone Bill05.22
53/2/20223-202206.22 Phone Bill06.22
63/2/20223-202207.22 Phone Bill07.22
73/2/20223-202208.22 Phone Bill08.22
83/2/20223-202209.22 Phone Bill09.22
9 
10 
11Invoice DatePosting MonthRepairs Expense 
123/2/20223-202203/02 Repairs03/02
133/2/20223-202203/07 Repairs03/07
143/2/20223-202203/09 Repairs03/09
153/2/20223-202203/02 Repairs03/02
16 
17 
18Invoice DatePosting MonthInternet 
193/2/20223-202201/05-02/04 Internet01/05-02/04
203/2/20223-202202/05-03/04 Internet02/05-03/04
Sheet1052
Cell Formulas
RangeFormula
D2:D20D2=IF(ISERR(LEFT(C2)+0),"",LEFT(C2,FIND(" ",C2)-1))
Click to expand...
Thank you and Sufiyan for taking the time to try to help me out with this. Both formulas work but this one was a bit closer to what I need.

I did not scrutinize my data enough prior to asking the question. Hopefully you can assist me with this, if not it is okay as what you and Sufiyan provided is wonderful

Sometimes it says "Internet Service 01/05-02/04" So the numbers are on the right side. Any way of making the formula extract those as well?
 
Upvote 0
Coyotex3 said:
Sometimes it says "Internet Service 01/05-02/04" So the numbers are on the right side. Any way of making the formula extract those as well?
Click to expand...

Modified to suit your new requirement:

Book3.xlsx
ABCD
1Invoice DatePosting MonthPhone ExpenseDates
23/2/20223-202203.22 Phone Bill03.22
33/2/20223-202204.22 Phone Bill04.22
43/2/20223-202205.22 Phone Bill05.22
53/2/20223-202206.22 Phone Bill06.22
63/2/20223-202207.22 Phone Bill07.22
73/2/20223-202208.22 Phone Bill08.22
83/2/20223-202209.22 Phone Bill09.22
9 
10 
11Invoice DatePosting MonthRepairs Expense 
123/2/20223-202203/02 Repairs03/02
133/2/20223-202203/07 Repairs03/07
143/2/20223-202203/09 Repairs03/09
153/2/20223-202203/02 Repairs03/02
16 
17 
18Invoice DatePosting MonthInternet 
193/2/20223-202201/05-02/04 Internet01/05-02/04
203/2/20223-202202/05-03/04 Internet02/05-03/04
21Internet Service 01/05-02/0401/05-02/04
Sheet1052
Cell Formulas
RangeFormula
D2:D21D2=IF(ISNUMBER(LEFT(C2)+0),LEFT(C2,FIND(" ",C2)-1),IF(ISNUMBER(RIGHT(C2)+0),TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),""))
 
Upvote 0
Solution
jtakw said:
Modified to suit your new requirement:

Book3.xlsx
ABCD
1Invoice DatePosting MonthPhone ExpenseDates
23/2/20223-202203.22 Phone Bill03.22
33/2/20223-202204.22 Phone Bill04.22
43/2/20223-202205.22 Phone Bill05.22
53/2/20223-202206.22 Phone Bill06.22
63/2/20223-202207.22 Phone Bill07.22
73/2/20223-202208.22 Phone Bill08.22
83/2/20223-202209.22 Phone Bill09.22
9 
10 
11Invoice DatePosting MonthRepairs Expense 
123/2/20223-202203/02 Repairs03/02
133/2/20223-202203/07 Repairs03/07
143/2/20223-202203/09 Repairs03/09
153/2/20223-202203/02 Repairs03/02
16 
17 
18Invoice DatePosting MonthInternet 
193/2/20223-202201/05-02/04 Internet01/05-02/04
203/2/20223-202202/05-03/04 Internet02/05-03/04
21Internet Service 01/05-02/0401/05-02/04
Sheet1052
Cell Formulas
RangeFormula
D2:D21D2=IF(ISNUMBER(LEFT(C2)+0),LEFT(C2,FIND(" ",C2)-1),IF(ISNUMBER(RIGHT(C2)+0),TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),""))
Click to expand...
You guys are absolute gods amongst men.
 
Upvote 0
jtakw said:
Modified to suit your new requirement:

Book3.xlsx
ABCD
1Invoice DatePosting MonthPhone ExpenseDates
23/2/20223-202203.22 Phone Bill03.22
33/2/20223-202204.22 Phone Bill04.22
43/2/20223-202205.22 Phone Bill05.22
53/2/20223-202206.22 Phone Bill06.22
63/2/20223-202207.22 Phone Bill07.22
73/2/20223-202208.22 Phone Bill08.22
83/2/20223-202209.22 Phone Bill09.22
9 
10 
11Invoice DatePosting MonthRepairs Expense 
123/2/20223-202203/02 Repairs03/02
133/2/20223-202203/07 Repairs03/07
143/2/20223-202203/09 Repairs03/09
153/2/20223-202203/02 Repairs03/02
16 
17 
18Invoice DatePosting MonthInternet 
193/2/20223-202201/05-02/04 Internet01/05-02/04
203/2/20223-202202/05-03/04 Internet02/05-03/04
21Internet Service 01/05-02/0401/05-02/04
Sheet1052
Cell Formulas
RangeFormula
D2:D21D2=IF(ISNUMBER(LEFT(C2)+0),LEFT(C2,FIND(" ",C2)-1),IF(ISNUMBER(RIGHT(C2)+0),TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),""))
Click to expand...
Works like a charm! Thank you so much. This is my first time utilizing a formula besides "Alt + =" Thank you!
 
Upvote 0
You're very welcome, thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
212
Peter_SSs
Peter_SSs
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
818
Dave Patton
D
SaraWitch
  • Solved
Calculate number of days between two dates when one is created by another formula
Replies
2
Views
390
SaraWitch
SaraWitch
G
  • Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
350
Trebor76
T
F
  • Question
Need to find and highlight values from high column which are greater than 10 times low value but on same expiry date,same strike price,same option typ
Replies
3
Views
306
etaf
etaf

Forum statistics

Threads
1,214,810
Messages
6,121,690
Members
449,048
Latest member
81jamesacct

Share this page

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