Forum Discussion

Vinma7's avatar
Vinma7
Copper Contributor
Jun 24, 2022

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 Qu...
Excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Jun 24, 2022

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)  )
  • Vinma7's avatar
    Vinma7
    Copper Contributor
    Jun 26, 2022
    @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.
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jun 26, 2022

      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.

Resources