Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Two Questions, please help

Copper Contributor

I really need some guidance about these two situation please help. 

  1. 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:

Cottami Lane
I am Courageous Knight


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.



5 Replies


You wrote: 

  1. 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.


That could be something like

  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)  )


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.


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.