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:

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

5 Replies

@Vinma7 

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.

@Vinma7 

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

@mathetes 

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

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

@Vinma7 

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.