Formula to rearrange text in a cell

[Nyx2012]

Hi, I have the following date and time stamp 15/05/2023 11:25:35 which I want to rearrange to the following:
2023-05-15 11:25:35. I have tried the normal format cells route but it does not want to comply.

Please could someone assist?

 

[Nyx2012]

=IF(ISNUMBER(A2), DATE(YEAR(A2),DAY(A2),MONTH(A2)), DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2)))

Did you want a date as a result or Text ? - text is fine
And do you have more dates and do some of the change when you change the date format and not others ? dates are extracted from a website and stored as text.
In B2 you could try the below and the format the cell the way you want to see it. - the formula returns an error

 

[Nyx2012]

Welcome to the MrExcel board!


I assume then that the original value is Text and not Date (Numerical). If that is so, you could try this:

I manually entered the formula, following the prompts from excel and came up with this one which works in 365:

=TEXTJOIN("-";;MID(G2;{7;4;1};{4;2;2}))&RIGHT(G2;9)

 

[Peter_SSs]

I manually entered the formula, following the prompts from excel and came up with this one which works in 365:

=TEXTJOIN("-";;MID(G2;{7;4;1};{4;2;2}))&RIGHT(G2;9)

Good news. Apparently you use an Excel version that requires ; as the formula separator, not , as in the standard English versions.
You will need to keep that in mind with any other suggestions that you might read in the forum.
Thanks for letting us know.

 

[AFZAL SOHAIL]

Sir, I have an question,
=TEXTJOIN("-";;MID(G2;{7;4;1};{4;2;2}))&RIGHT(G2;9)
what would be this formula In excel 2016?

 

[Peter_SSs]

Sir, I have an question,
=TEXTJOIN("-";;MID(G2;{7;4;1};{4;2;2}))&RIGHT(G2;9)
what would be this formula In excel 2016?

Try

=MID(G2;7;4)&"-"&MID(G2;4;2)&"-"&MID(G2;1;2)&RIGHT(G2;9)

 

[AFZAL SOHAIL]

Thanks a lot Sir,
Mr.Peter_SSs.
I use your formula as put the given in A29, but it giving some error, after that I use comma(,), instead of Semi colon in the formula,
it gives a value like this:- Kindly need your help I used excel 2016

15/05/2023 11:25:35​	=MID(a29;7;4)&"-"&MID(a29;4;2)&"-"&MID(a29;1;2)&RIGHT(a29;9)
	4760-61-45476099537 when I used comma, instead of semi colon

 

[Peter_SSs]

I use your formula as put the given in A29, but it giving some error, after that I use comma(,), instead of Semi colon in the formula

I used semi-colon because that is what you used in post 14 & asked to convert to Excel 2016.

The original question in this thread was for text data, not numbers. It appears that your data is numbers, not text. Can you confirm that?

Could we have 4 or 5 samples of your data and the expected results & explain again in relation to those samples?

 

[AFZAL SOHAIL]

Hello respected Sir,
I am share more example:-Col-A have dates changes it into the format col-B, and col-C applied formula using comma,
Formulas used in col-b is TEXT(A1,"yyyy/mm/dd hh:mm:ss") and in Col-C, I put this formula =MID(B1,7,4)&"-"&MID(B1,4,2)&"-"&MID(B1,1,2)&RIGHT(B1,9)it gives the value as it under:-

16-05-23 11:30	2023/05/16 11:30:30	5/16-3/-20 11:30:30
17-05-23 11:30	2023/05/17 11:30:30	5/17-3/-20 11:30:30
18-05-23 11:30	2023/05/18 11:30:30	5/18-3/-20 11:30:30
19-05-23 11:30	2023/05/19 11:30:30	5/19-3/-20 11:30:30
20-05-23 11:30	2023/05/20 11:30:30	5/20-3/-20 11:30:30
21-05-23 11:30	2023/05/21 11:30:30	5/21-3/-20 11:30:30
22-05-23 11:30	2023/05/22 11:30:30	5/22-3/-20 11:30:30

 

[Peter_SSs]

It appears that your data is numbers, not text. Can you confirm that?

I didn't see a response to this.

Could we have .... and the expected results

Does the previous post contain the expected results as requested?
If so, which column is the expected results?
If not, please post the expected results for that sample data

 

[AFZAL SOHAIL]

I am sharing again the results which is not expected and giving the wrong values:Col-b dates are in text and col-A is numerical, use formula in Col-C it gives the values are showing and then use this formula in col-A it gives the value are also shows:-

sample dates	CONVERT INTO TEXT	RESULTS COMES OUT	USE A-COL AS NUMERICAL VALUE
16-05-23 11:30	2023/05/16 11:30:00	5/16-3/-20 11:30:00	4791-62-45791666667
17-05-23 11:30	2023/05/17 11:30:00	5/17-3/-20 11:30:00	4791-63-45791666667
18-05-23 11:30	2023/05/18 11:30:00	5/18-3/-20 11:30:00	4791-64-45791666667
19-05-23 11:30	2023/05/19 11:30:00	5/19-3/-20 11:30:00	4791-65-45791666667
20-05-23 11:30	2023/05/20 11:30:00	5/20-3/-20 11:30:00	4791-66-45791666667
21-05-23 11:30	2023/05/21 11:30:00	5/21-3/-20 11:30:00	4791-67-45791666667
22-05-23 11:30	2023/05/22 11:30:00	5/22-3/-20 11:30:00	4791-68-45791666667