Google-sheets – Split Text and reorganize and concatenate back together

concatenategoogle sheetsworksheet-function

I want a formula to split data "052 089652" and to reorganize it as "089652/A052/9" in Google Sheets. The formula needs to add "A" to the start of "052", add a "9" at the end, keep the leading zero, if there is one and place "/" in between. Another example below:

Input: 125 896542


I can do it using SPLIT and CONCATENATE functions in different cells but it gets messy.

Is there a simpler "all in one formula" to do this?

Best Answer

Assuming You provided a exact format of data,

=REGEXREPLACE(F2,"(\d+) (\d+)", "$2/A$1/9")