Forum Discussion
Two Questions, please help
I really need some guidance about these two situation please help.
- My data looks something like this:
| 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
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) )- @sergei
Thank you for your help. Quick stupid question, do I need to change anything in this command or run in any cell. I considering the data which I have put in the cell but I am getting a Name error when I am running it straight away.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.
You wrote:
- My data looks something like this:
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.
