google sheets
I would like to ask if it is possible to split and transpose a group of words from one cell to multiple cells in a spreadsheet. This is what it looks like in cell A1:
Zhen Rong Yu Merrill Lynch, Pierce, Fenner & Smith Incorporated
9595 WILSHIRE BLVD
BEVERLY HILLS, CA 90212
I would like to know if its possible to look like this:
A1: Zhen Rong Yu
B1: Merrill Lynch, Pierce, Fenner & Smith Incorporated
C1: 9595 WILSHIRE BLVD
D1: BEVERLY HILLS, CA 90212
Sounds like the =SPLIT function would do what you need. Given that A1 contains the text 1 coffeeextradark 240gr, then
=SPLIT(A1, " ")
(notice the space between the quotation marks)
will split the text into three cells:
1 | coffeeextradark | 240gr
You can use a quite pragmatic approach to reach your goal.
=ARRAYFORMULA(
SPLIT( // string
REGEXREPLACE(
A1:A4, // text
"[users,<=>compani]", // regular_expression
"" // replacement
),
" ") // delimiter
)
copy/paste
=ARRAYFORMULA(SPLIT(REGEXREPLACE(A1:A4, "[users,<=>compani]",""), " "))
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.
Best Answer
This may get you started:
But:
The results would not start in A1 (which can't hold a value and a formula at the same time). Maybe enter it in B1, select ColumnB, Copy, Paste Special, Paste values only and then delete ColumnA.
There is no way to automate splitting
Zhen Rong YufromPierce, Fenner & Smith Incorporatedunless there is some consistency between the first or second parts with examples not shown, or there is a delimiter (added manually?) between the two parts, or you have a lookup table to show which part is which.You may end up with needing to filter (or equivalent) to delete blank rows, depending upon what is causing the blank rows (if any) in A1.
TRANSPOSE
SPLIT
SUBSTITUTE