Reorder text string from within one cell with a different separator

[bonobono]

so I’m pasting in a text in A1, which is a date (but just pasted in as text). it’s format is dd.mm.yyyy
i’d like B2 to produce a result of yyyy/mm/dd (it doesn’t need to be formatted as a date, i’m only going to paste it over later.

in A2 however, I’m using ctrl+; to insert current date. will the same formula produce a reorganized version of it in B2? I know I could change to a different ISO of the document and have ctrl+; produce my desired effect immediately, but for reasons I’m not going to go into I prefer not to.

what do?

thx in advance!

[Lupp]

The second case first:
If you inserted the date using the shortcut into A2, and it is shown right aligned in the default format of the used locale, it is contained there as a number (as spreadsheets represent dates by default) and cannot be “reorganized”, but must be reformatted. The formula in B2 should then simply be =A2, and the Numbers format for B2 set by the code YYYY-MM-DD (not using the stubborn slashes violating ISO 8601). If you want to get the result in B2 as text , you can use the same format code with the TEXT() function: =TEXT(B2;"YYYY-MM-DD")

In the first case the content of A1 is text as you said. You can use
=Right(A1;4) & "-" & MID(A1;4;2) & "-" & LEFT(A1;2), but for this requires the assurance that no leading zero (M or D) is left out. If this cannot be assured, and YourLibO is V6.2 or higher, you can use
=TEXTJOIN("-";0;REGEX(A1;"\d+";;{3;2;1})) entered for array-evaluation (Ctrl+Shift+Enter). Having the bad setting of the comma as the column delimiter in inline arrays, you may need to replace {3;2;1} with {3,2,1}.

[bonobono]

Thank you Lupp,

unfortunately, I do need the fwd slash, not my choice.

With a little tweaking I got my result.

[Lupp]

Who chose?
It’s funny. We founded, we maintain, and we pay for International Organizations of Standardization (not only ISO) where well informed people research the background, develop and negotiate proposals, and finally publish standards. Then we ignore these standards or apply arbitrary and stubborn modifications, this way destroying most of the value we gained.
That’s human?
Well, the replacement of a hyphen by a slash isn’t the most abominable violation of ISO 8601, but I cannot find any thinkable advantage…
“Tweak” should mean something like improve, refine, optimize. That’s not what you did. Might you have fiddled?