5

I want to split a cell, reorder the pieces and recombine. For example:

| name        | reordered name |
| Page,Larry  | Larry Page     |
| Brin,Sergey | Sergey Brin    |

I know that =SPLIT(A2, ",") will split the values into the next two columns and I know that I can concatenate values with =CONCATENATE(A2, " ", B2).

How do I bring these things together in a single formula?

Can I access the values from SPLIT and reorder them?

I know I can do =CONCATENATE(SPLIT(A2)) (but obviously that is stupid).

In Ruby I would just do something like:

def reorder_name(cell)
  names = cell.split(",")
  "#{names[1]} #{names[0]}"
end
5

With the following custom function (as you're accustomed to writing in JavaScript) it is possible as well:

Code

function myReverse(range) {
  var output = [];
  if(range[0].length > 1) {
    throw ("function can only handle a column range");
  } else {
    for(var i in range) {
      var name = range[i][0].split(",");
      output.push([name.reverse().join(" ")]);
    }
  }
  return output;
}

Remark

Small error handling throws a message when range contains more than 1 column. Functions similar to ARRAYFORMULA to accept ranges. Add code under Tools, Script editor. Save the script and you're ready to go !!

Formula

The following formula will do the trick also:

=CONCATENATE(INDEX(SPLIT(A2, ","),1,2)," ",INDEX(SPLIT(A2, ","),1,1))

The INDEX function allows you to select particular cells, from a CONTINUED range. In this case you want the second split result to appear first, thus the ,1,2, meaning first row, second column of the split result.

Example

I've prepared an example file for you: split and reorder

2

Use these GSheet functions to reverse the order of the words in a string.

Write in cell A1 ="TRICK NO THIS IS"
and in cell B1 =JOIN(" ";TRANSPOSE(SORT(TRANSPOSE(SPLIT(A1;" "));1;FALSE)))

It creates IS THIS NO TRICK from the input in A1.

Read the formula from inside to outside:

  • SPLIT creates and array in a row ("virtual" if the formula is used as shown) of the words in A1 separated by blanks
  • TRANSPOSE makes a vertical array from the horizontal array
  • SORT sorts a vertical array, 1 = single column and FALSE reverses the order
  • TRANSPOSE creates again a horizontal array from the vertical reordered array
  • JOIN concatenates the horizontal array of words into a string with blanks in betweeen in B1.
1
=CONCATENATE(MID(A1,FIND(",",A1)+1,999), " ",LEFT(A1,FIND(",",A1)-1))

Some guy on this thread posted how to get the bits, I just wrapped them in the concat.

Note that the "+1" in the +1,999 part will be your adjustment if there are spaces or not after the comma. Since yours does not have a space, I just dropped it back to 1 instead of 2 like in the referenced page.

1

It is also possible to split firstname lastname into two columns, add a comma delimiter and reverse order. Very useful for correction payroll entries.

            A               B            C
| firstname lastname  |  lastname  | firstname |
| JOHN BROWN          |  BROWN,    | JOHN      |
| MARTHA GREEN        |  GREEN,    | MARTHA    |

in column B

=SPLIT(TRIM(MID($A1,FIND(" ",$A1,1)+1,100)) 
&", "& TRIM(LEFT($A1,FIND(" ",$A1,1)-1))," ",FALSE)
0

Also possible with regular expressions:

=regexreplace(A1,"[A-z]+,","")&" "&regexextract(A1,"\w*")
0
name             reordered name
Page,Larry       Larry Page 
Brin, Sergey     Sergey Brin

formula:

=index(split(A2, ","), 0, 2)&" "&index(split(A2, ","), 0, 1)

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.