Jun 24 2022 09:29 AM - edited Jun 24 2022 09:30 AM
I really need some guidance about these two situation please help.
3.50 Earth 3.75 Sezer 4.33 Cottami Lane |
1.67 I am Courageous Knight 6.00 Ezaji 8.00 Quinault |
The above is in one cell and I want to make it something like:
Earth |
Sezer |
Cottami Lane |
I am Courageous Knight |
Ezaj |
Quinault |
First I tried Flash Fill and got 3 columns of each names. Then I transposed each row, however, next two cells were occupied so I got Spill error. Kindly help.
Regards
Vinma
Jun 24 2022 12:22 PM
You wrote:
So presumably there are other instances where the "something like" has multiple words to be put together, sometimes two words, sometimes three, sometimes four....
A first question back to you would be "What's the source of this data?" Followed by "Can that source be directed to provide the data in the manner you desire?"
The inconsistencies here are the biggest part of the challenge. That's why I suggest first seeing if the source could be modified.
Jun 24 2022 02:12 PM
That could be something like
=LET(
str, A1,
sep, UNICHAR(9999),
values, TEXTSPLIT(str, IF( {1,0}, " ", CHAR(10) ) ),
addSeps, IF( ISNUMBER(--values), sep, values ),
txt, CONCAT( addSeps ),
adjust, IF( LEFT(txt) = sep, RIGHT( txt, LEN(txt) - 1 ), txt ),
TEXTSPLIT( adjust, ,sep) )
Jun 26 2022 12:24 AM
Thanks for your answer. I understand your point. However, first it cannot be acquired in the way I want it. Second, the no. of words in each cell is maximum 1 or 2 or 3 for about 99% times and rarely 4.
Regards
Jun 26 2022 12:36 AM
Jun 26 2022 04:16 AM
Cell with the text is defined within formula, in the sample that's A1.
If #NAME? error - most probably function used (TEXTSPLIT, LET,...) are not available in your version of Excel.