google sheets
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?
Ok, I think I've figured it out. Not straightforward, but now that it's done, not terribly difficult.
**=VLOOKUP(D1,'TARGET_SHEET'|A:B,2)**
Some explanations about why I couldn't get the answer.
Try the following solution.
=ARRAYFORMULA(TRIM(SPLIT(JOIN(" ,", A1:A4,C1:C4), ",")))
You already showed us that the SPLIT function ignores empty cells, therefore I choose this string as delimiter: " ,". The intermediate result of the JOIN function, looks like this:
I choose the delimiter for the SPLIT function to be this: ",". The intermediate result of the SPLIT function, looks like this:
Now, however, we have to deal with the extra added white spaces. Therefore I used the TRIM function, to remove all trailing or leading white spaces (in combination with the ARRAYFORMULA). Compare the LEN values of each intermediate result.
I've created an example file for you: SPLIT to preserve blank cells
Best Answer
You can use a quite pragmatic approach to reach your goal.
Formula
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 theSPLITis set accordingly and all is put into anARRAYFORMULA.