1

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

Output:896542/A125/9

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?

2

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 '17 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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