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?

1 Answer 1


Assuming You provided a exact format of data,

=REGEXREPLACE(F2,"(\d+) (\d+)", "$2/A$1/9")
  • That's great, is there a similar formula for excel. as regexreplace doesn't work in excel. Sorry didn't mention that in the original post but noticed this might need recreating in excel.
    – Alan Allen
    Oct 26, 2017 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.