Split, reorder, concat inline

Question

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

Jacob Jan · Accepted Answer · 2013-09-12T10:31:20.430

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

score 2 · Answer 2 · edited Sep 08 '18 at 13:09

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.

score 1 · Answer 3 · answered Sep 09 '13 at 20:13

=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.

score 1 · Answer 4 · edited Sep 07 '16 at 05:36

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)

score 0 · Answer 5 · edited Sep 08 '18 at 13:05

0

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

formula:

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

edited Sep 08 '18 at 13:05

user0

27,169
10
55
81

answered Sep 08 '18 at 12:58

psima

1

score 0 · Answer 6 · answered Apr 30 '14 at 19:07

0

Also possible with regular expressions:

=regexreplace(A1,"[A-z]+,","")&" "&regexextract(A1,"\w*")

answered Apr 30 '14 at 19:07

pnuts

17,883
5
55
103

Split, reorder, concat inline

6 Answers6

Code

Remark

Formula

Example