Extract dd.mm.yyyy string from a text and convert it to dd.mm.yyyy date format

V

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
348
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello!

I have a column with cells that contain text strings. Some cells include in the text a date, structured as dd.mm.yyyy, whereas other ones are without it. I need to extract in the next column all dates from strings, converted from text to date format. I mention I use Microsoft 365, but on this PC I need to use Office 2019.
Thank you!

Book1
AB
1Original string Desired results
2ABF – urmează programareBlank
3APAT – urmează programareBlank
4APCG – 11.12.202411.12.2024
5APF – 09.12.202409.12.2024
6APG – urmează programareBlank
7ASK – 04.12.202404.12.2024
8MPBP – 05.12.202405.12.2024
9MPCH – 02.12.202402.12.2024
10MPPC – 04.12.202404.12.2024
11MPPF – 09.12.202409.12.2024
12MPSK – 03.12.202403.12.2024
13MPSW – 29.11.202429.11.2024
14SFBP – 09.12.202409.12.2024
15SFP – 28.11.2024, în jur de ora 1328.11.2024
16SFSK – 04.12.202404.12.2024
Sheet1
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Your date format looks pretty clean, if you're regional setting is dd.mm.yyyy does this work for you ?
Excel Formula: 
=IFERROR(--MID(A2,FIND(".",A2)-2,10),"")
 
Upvote 0
This should help.
Book1
ABC
1Original stringDesired results
2ABF – urmează programareBlank 
3APAT – urmează programareBlank 
4APCG – 11.12.202411.12.202411.12.2024
5APF – 09.12.202409.12.202409.12.2024
6APG – urmează programareBlank 
7ASK – 04.12.202404.12.202404.12.2024
8MPBP – 05.12.202405.12.202405.12.2024
9MPCH – 02.12.202402.12.202402.12.2024
10MPPC – 04.12.202404.12.202404.12.2024
11MPPF – 09.12.202409.12.202409.12.2024
12MPSK – 03.12.202403.12.202403.12.2024
13MPSW – 29.11.202429.11.202429.11.2024
14SFBP – 09.12.202409.12.202409.12.2024
15SFP – 28.11.2024, în jur de ora 1328.11.202428.11.2024
16SFSK – 04.12.202404.12.202404.12.2024
Sheet1
Cell Formulas
RangeFormula
C2:C16C2=IFERROR(MID(A2,SEARCH("??.??.????",A2),10)+0,"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Extracting number from text string
Replies
4
Views
578
Fluff
Fluff
J
  • Question Question
Formatting error when switching from Mac to PC
Replies
6
Views
84
RoryA
RoryA
J
  • Question Question
Extract a date from a string of text
Replies
4
Views
191
Scott Huish
Scott Huish
P
  • Question Question
Extract the word which carries the specific text
Replies
5
Views
206
Cubist
Cubist
D
  • Solved
Timestamp reads as text when converting to Date
Replies
6
Views
73
Joe4
Joe4

Forum statistics

Threads
1,224,527
Messages
6,179,345
Members
452,907
Latest member
Roland Deschain

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