4

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:

1300  
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
2

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

Formula

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

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

Screenshot

enter image description here

Explained

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. – Jacob Jan Tuinstra Jan 9 '16 at 20:12
1

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.