2

How do I join a to both sides of : to result in a:a?

I could use something like =join(":","a","a"), but in this case, my "a" is another formula and it is very long. I'm trying to find a way so I don't have to write the formula twice.

Something like

=join(":",rept("a",2)) → but this produces aa

migrated from stackoverflow.com Nov 5 '14 at 17:56

This question came from our site for professional and enthusiast programmers.

2

Try this formula.

Formula

=REGEXREPLACE(REPT(A1 & ":",2),".$","")

Explained

First the following string is being repeated twice: RESULT_OF_A1 combined with a semicolon. The last semicolon in this repetition is being removed by the REGEXREPLACE formula. It simply replaces the last character by an empty space. The result of your formula is being used only once.

  • unfortunately that won't work, that is the same thing as =CONCATENATE(A1, ":", A1). I'm trying to keep in the same cell with one formula only. In this case, the A1 formula will be called twice. – jason Nov 5 '14 at 14:26
  • @jason_cant_code How about this? – Jacob Jan Tuinstra Nov 5 '14 at 14:29
  • Both A has to be produced by a formula. I'm trying to avoid typing in the formula twice. I also want to keep it in one cell. – jason Nov 5 '14 at 14:38
  • 1
    Works. Nice solution. – jason Nov 5 '14 at 16:25

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.