Convert text information to excel date format

[beca]

Hi all, I have a spreadsheet generated from a software with date information. This date information is a text, not in excel date format.

I would like to convert this text to excel date format dd/mm/yyyy.

The spreadsheet looks like below.

Thank you all.

 

[FormR]

the formula doesn't work

Hi, if interested in a formula, here's one you could try if your regional settings are in the UK/EU style and you are using XL365.

Book3
	A	B
1	Transaction Apply Date	Formula
2	May 12, 2023	12/05/2023
3	May 12, 2023	12/05/2023
4	May 12, 2023	12/05/2023
5	Jun 5, 2023	05/06/2023
6	Jul 7, 2023	07/07/2023
7	Jul 7, 2023	07/07/2023
8	Jul 7, 2023	07/07/2023
9	Jul 14, 2023	14/07/2023
10	Apr 7, 2023	07/04/2023
11	Apr 25, 2023	25/04/2023
12	Apr 25, 2023	25/04/2023
13	Apr 14, 2023	14/04/2023
14	Apr 14, 2023	14/04/2023
15	Apr 14, 2023	14/04/2023
16	Apr 10, 2023	10/04/2023
17	Sep 26, 2022	26/09/2022
18	Oct 24, 2022	24/10/2022
19	Oct 13, 2022	13/10/2022
20	Oct 12, 2022	12/10/2022
21	Nov 11, 2022	11/11/2022
22	Nov 1, 2022	01/11/2022
23	Nov 1, 2022	01/11/2022
24	Jun 6, 2022	06/06/2022
25	Jun 5, 2023	05/06/2023
26	Jun 26, 2023	26/06/2023
Sheet1

Cell Formulas
Range	Formula
B2:B26	B2	=0+CONCAT(INDEX(TEXTSPLIT(A2,{" ",","},,1),{2,1,3}))

 

[MARK858]

I don't think you have to go past the first dialog page (see my reply to the OP above).

I would do

Use Data TextToColumns
Next
Check that all the checkboxes aren't checked
Next
Date: select MDY
Click Finish

because if the Comma separator is selected then

Book1.xlsb
	A
1	Transaction Apply Date
2	May 12, 2023
3	May 12, 2023
4	May 12, 2023
5	Jun 5, 2023
6	Jul 7, 2023
7	Jul 7, 2023
8	Jul 7, 2023
9	Jul 14, 2023
10	Apr 7, 2023
11	Apr 25, 2023
12	Apr 25, 2023
13	Apr 14, 2023
14	Apr 14, 2023
15	Apr 14, 2023
16	Apr 10, 2023
17	Sep 26, 2022
18	Oct 24, 2022
19	Oct 13, 2022
20	Oct 12, 2022
21	Nov 11, 2022
22	Nov 1, 2022
23	Nov 1, 2022
24	Jun 6, 2022
25	Jun 5, 2023
26	Jun 26, 2023
Sheet3

becomes

Book1.xlsb
	A	B	C
1	Transaction Apply Date
2	12-May	2023
3	12-May	2023
4	12-May	2023
5	05-Jun	2023
6	07-Jul	2023
7	07-Jul	2023
8	07-Jul	2023
9	14-Jul	2023
10	07-Apr	2023
11	25-Apr	2023
12	25-Apr	2023
13	14-Apr	2023
14	14-Apr	2023
15	14-Apr	2023
16	10-Apr	2023
17	26-Sep	2022
18	24-Oct	2022
19	13-Oct	2022
Sheet3

 

[Rick Rothstein]

I would do

Use Data TextToColumns
Next
Check that all the checkboxes aren't checked
Next
Date: select MDY
Click Finish

because if the Comma separator is selected then

Book1.xlsb
	A
1	Transaction Apply Date
2	May 12, 2023
3	May 12, 2023
4	May 12, 2023
5	Jun 5, 2023
6	Jul 7, 2023
7	Jul 7, 2023
8	Jul 7, 2023
9	Jul 14, 2023
10	Apr 7, 2023
11	Apr 25, 2023
12	Apr 25, 2023
13	Apr 14, 2023
14	Apr 14, 2023
15	Apr 14, 2023
16	Apr 10, 2023
17	Sep 26, 2022
18	Oct 24, 2022
19	Oct 13, 2022
20	Oct 12, 2022
21	Nov 11, 2022
22	Nov 1, 2022
23	Nov 1, 2022
24	Jun 6, 2022
25	Jun 5, 2023
26	Jun 26, 2023
Sheet3

becomes

Book1.xlsb
	A
1	Transaction Apply Date
2	May 12, 2023
3	May 12, 2023
4	May 12, 2023
5	Jun 5, 2023
6	Jul 7, 2023
7	Jul 7, 2023
8	Jul 7, 2023
9	Jul 14, 2023
10	Apr 7, 2023
11	Apr 25, 2023
12	Apr 25, 2023
13	Apr 14, 2023
14	Apr 14, 2023
15	Apr 14, 2023
16	Apr 10, 2023
17	Sep 26, 2022
18	Oct 24, 2022
19	Oct 13, 2022
20	Oct 12, 2022
21	Nov 11, 2022
22	Nov 1, 2022
23	Nov 1, 2022
24	Jun 6, 2022
25	Jun 5, 2023
26	Jun 26, 2023
Sheet3

Good point about the comma delimiter !