Trim formula working on one cell, but not working on similar adjacent cell

[WikeMo1]

Hello All,
This thing has stumped me.

I trying to separate a bullet point list from one cell into different columns using this formula:
=TRIM(LEFT(SUBSTITUTE(MID(D4,FIND("|",SUBSTITUTE(D4,"•","|",1))+1,LEN(D4)),"•",REPT(" ",LEN(D4))),LEN(D4)))


It works most of the time, but sometimes it doesn't and I cannot figure out why.

For example,
I have two cells here and one works, the other doesn't. I have found that if I reduce the characters in the first cell by deleting the text in red, the formula works.
What is going on here? Is this a character limit issue?
[TABLE="width: 500"]
<tbody>[TR]
[TD]

<tbody>
[TD="class: xl65"]• Elegant All White Fra me Design • Super Lightweight Powder Coated Aluminum Frame Construction • Detachable Pin-Hinged Sections for Easy Assembling and Disassembling • Includes Four 24"W x 46"H Sections • Each Section Color Code Marked For a Quick Setup • Fire Retardant Treated Dual Scrim Screen Faces • Use in Conjunction with Lighting for Color Wash Effects • Carrying/Storage Bag is Included: (25" x 46.75" x 4.25") *use all necessary safety precautions when using heat and electrical related devices near the stretch screen **SCRIM CLEANING CARE: Dry-Clean or Machine wash in cold water (Do not bleach). Hang dry or Tumble dry at "low" to no heat. Do not iron. *Flame retardant must be retreated after washing or dry-cleaning. Other Related Products • SWF7246WHT 72"W x 46"H All White Pro DJ Facade ALL BLACK PRO DJ FACADES • SWF4846BLK 48"W x 46"H All Black Pro DJ Facade • SWF7246BLK 72"W x 46"H All Black Pro DJ Facade BLACK FRAME WITH WHITE SCRIM PRO DJ FACADES • SWF4846B 48"W x 46"H Pro DJ Facade • SWF7246B 72"W x 46"H Pro DJ Facade • SWF9646B 96"W x 46"H Pro DJ Facade • SWFTT5816B 58"W x 16"H Table Top Pro DJ Facade OTHER SCRIM WERKS™ PRODUCTS • Light Columns • 12"x12" Square Truss Slip Screens™ Stretch Covers • Banquet Table Slip Screens™ Stretch Covers • Lighting/Speaker Tripod/Crank Stand Stretch Covers • X Stand Slip Screen™ Stretch Covers • Video Stretch Screens • Triangular Decor Scrims • Decor Display Panels[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65, align: center"]#VALUE![/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

• Sleek All Black Stealth Frame and Scrim Design • Super Lightweight Powder Coated Aluminum Frame Construction • Detachable Pin-Hinged Sections for Easy Assembling and Disassembling • Includes Four 24"W x 46"H Sections • Each Section Color Code Marked For a Quick Setup • Carrying/Storage Bag is Included: (25" x 46.75" x 4.25") *use all necessary safety precautions when using heat and electrical related devices near the stretch screen **SCRIM CLEANING CARE: Dry-Clean or Machine wash in cold water (Do not bleach). Hang dry or Tumble dry at "low" to no heat. Do not iron. *Flame retardant must be retreated after washing or dry-cleaning. Other Related Products • SWF7246BLK 72"W x 46"H All Black Pro DJ Facade ALL WHITE PRO DJ FACADES • SWF4846WHT 48"W x 46"H All White Pro DJ Facade • SWF7246WHT 72"W x 46"H All White Pro DJ Facade BLACK FRAME WITH WHITE SCRIM PRO DJ FACADES • SWF4846B 48"W x 46"H Pro DJ Facade • SWF7246B 72"W x 46"H Pro DJ Facade • SWF9646B 96"W x 46"H Pro DJ Facade • SWFTT5816B 58"W x 16"H Table Top Pro DJ Facade OTHER SCRIM WERKS™ PRODUCTS • Light Columns • 12"x12" Square Truss Slip Screens™ Stretch Covers • Banquet Table Slip Screens™ Stretch Covers • Lighting/Speaker Tripod/Crank Stand Stretch Covers • X Stand Slip Screen™ Stretch Covers • Video Stretch Screens • Triangular Decor Scrims • Decor Display Panels

<tbody>
</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65"]Sleek All Black Stealth Frame and Scrim Design[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]


Here is an online workbook with the example. https://1drv.ms/x/s!Avza4YgA-BnDjBwMZ6nL1YudMvzq

 

[Fluff]

Are you trying to split the entire cell in separate columns, or just get the first value?

 

[WikeMo1]

Are you trying to split the entire cell in separate columns, or just get the first value?

Sorry, yes that formula is only for the first value. I am then changing the instance in the formula for the other values.

I updated the online workbook to show that.

I guess my issue at this point is why it is missing the first value.

 

[Fluff]

If you want to split the whole thing then just use Text To Columns > Delimited > select other & in the box next to it type Alt 0149 (using the number keypad) > Finish

 

[WikeMo1]

I guess I could do that. Although I will have to do that in a separate sheet, since I only want max. the first 7 instances, to fit within an already defined template. So in a way that doesn't get me what I want, but in a way it does.

 

[Fluff]

On the third page of the wizard you can select columns & select "Do not import columns"

 

[Gerald Higgins]

I haven't looked at your file, but for me, the formula you posted in post #1 , using the first block of data, returns the result
"Elegant All White Frame Design"

In other words, I don't get the #VALUE error.

Obviously if you want to break up the whole string into multiple segments, then Fluff's suggestion is a very good one in my opinion.
EXCEPT - Fluff, when I select "Other" in the Text to Columns dialog box, I can paste in the symbol from the source text, but typing Alt 0149 doesn't seem to work for me.
I get it that 0149 is the code for that character.
Is there a trick here ?

 

[Fluff]

Did you use the number keypad, rather than the numbers above the main keyboard?

 

[Gerald Higgins]

Ah, that's the trick !
Press and hold the Alt key, and use the number keypad.
Thanks !
Think it's easier to copy / paste the character from the source text (avoids having to identify the code number for one thing) but it's good to know, cheers !

 

[Fluff]

If you would prefer to stick with a formula try, in E2 copied down & across
=TRIM(MID(SUBSTITUTE($D1,"•",REPT(" ",500)),COLUMN(A1)*500,500))