Is it possible to split on the second space only? I am looking for a method that doesn't require VBA, however, I will use it if that is the most effective option.



You could:

Replace the first space with a unique character or expression (e.g. [rep]):

SUBSTITUTE(A1," ","[REP]", 1)

Convert text to columns.

Replace the previous expression back to space: SUBSTITUTE(A1,"[REP]", " ").

Another two step solution would be split the text to columns, getting 3 columns, and then concatenate the two first columns, separated by a space. (e.g. =A6&" "&B6)

| improve this answer | |
  • SUBSTITUTE replaces all occurrences by default, if you want to replace only first one, then use SUBSTITUTE(A1," ","[REP]",1) – Máté Juhász Oct 12 '15 at 12:20
  • 1
    @Máté: You're right. Fixed. – CFreitas Oct 12 '15 at 12:51

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.