I have a Google Sheets with 1,500 cells containing items like:

1,300 users  
2236 users, 12 companies  
15000 users,153 companies  
<46,000 users, <200 companies  

I need to split this content into two columns that would look like:

2236      12
15000     153
46000     200

Commas don't succeed as the delimiter in a simple SPLIT() function, nor does spacing.

Any ideas what I can do here?

  • @pnuts I cannot make any assumptions about where and how the commas have been used. This data was collected by a freelancer and she simply copied/pasted data from many different sources.
    – sscirrus
    Feb 16 '14 at 23:11
  • @pnuts Yes, I would like the operation to be accurate and split users,153. I would be OK with doing a recursive operation if that's what's necessary.
    – sscirrus
    Feb 17 '14 at 5:21

You can use a quite pragmatic approach to reach your goal.


   SPLIT(                        // string
       A1:A4,                    // text
       "[users,<=>compani]",     // regular_expression
       ""                        // replacement
   " ")                          // delimiter

=ARRAYFORMULA(SPLIT(REGEXREPLACE(A1:A4, "[users,<=>compani]",""), " "))


enter image description here


The regular_expression is cleaning up the string, in preparation for the SPLIT. The replacement is simply set to contain an empty string. The result, yields a string that contains one or two values, separated by a white space. The delimiter in the SPLIT is set accordingly and all is put into an ARRAYFORMULA.

  • 1
    Was this with the old version of Sheets? I copied this exact data and formula and can't get it to output the array, the only output is 1300.
    – user79865
    Jan 9 '16 at 20:08
  • The old, just found the original sheet I prepared. Jan 9 '16 at 20:12

Rather a rigmarole but if I explain correctly should work, I suggest testing on a copy:

  1. select data (assumed to be ColumnA), Edit, Find and Replace..., Find [a-z]., select Search using regular expressions, Replace all.
  2. change Find to < , Replace all.
  3. in B1 put =split(a1," ") and copy down to suit.
  4. copy B:C to E1 and Edit, Paste Special...., Paste Values only.
  5. select ColumnF, Edit, Find and Replace..., Find s, Replace all.
  6. move or copy ColumnsE:F where required and delete sheet.

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.