Split text with Google Sheets

Question

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?

Answer 1

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

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.

Answer 2

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.