3 part text parse: left, mid and right

[Seattlites]

I'm stumped on this three part text parse in a pipe-delimited format. Help greatly appreciated!

[Phuocam]

You can use: Data => Text to Columns, or try:

=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",250)),COLUMNS($B$2:B2)*250-249,250))

[Seattlites]

I apologize for not clarifying that a formula is the best solution in this case because the values are not static. New values are being added constantly from a survey.

I tried the solution =TRIM(MID(SUBSTITUTE($A4,"|",REPT(" ",250)),COLUMNS($B$2:B4)*250-249,250)) and it appears to return the same data as =IF(ISERROR(LEFT(A2,FIND("|",A2)-1)),A2,LEFT(A2,FIND("|",A2)-1))

The formula I've tried using for the middle portion between both pipes does not work as intended: =MID(LEFT(A2,FIND("|",A2)-1),FIND("|",A2)+1,LEN(A2))
The formula I've tried using for the right portion returns everything after the first instead of only data after the last pipe: =IF(ISERROR(MID(A2,FIND("|",A2,1)+1,LEN(A2))),"",MID(A2,FIND("|",A2,1)+1,LEN(A2)))

[Rick Rothstein]

I tried the solution =TRIM(MID(SUBSTITUTE($A4,"|",REPT(" ",250)),COLUMNS($B$2:B4)*250-249,250)) and it appears to return the same data as =IF(ISERROR(LEFT(A2,FIND("|",A2)-1)),A2,LEFT(A2,FIND("|",A2)-1))

It is not clear from your response if you understood that you are supposed to put that formula in your first cell AND THEN copy it across for two more columns, then copy those three cells down to the bottom of your data.

[Seattlites]

It is not clear from your response if you understood that you are supposed to put that formula in your first cell AND THEN copy it across for two more columns, then copy those three cells down to the bottom of your data.

I have copied the formula across with no results in the MID and RIGHT columns. Apologies if this sounds obtuse, but the solution is not intuitive. Update attached.

[MrShorty]

Are restricted to Excel only? I often think the easiest way to parse delimited text is using Google Sheets and it's built in SPLIT() function.https://www.sheetaki.com/split-funct...google-sheets/

[Seattlites]

Are restricted to Excel only? I often think the easiest way to parse delimited text is using Google Sheets and it's built in SPLIT() function.https://www.sheetaki.com/split-funct...google-sheets/

SPLIT in Google Sheets is actually perfect, thank you MrShorty!

[Rick Rothstein]

I have copied the formula across with no results in the MID and RIGHT columns. Apologies if this sounds obtuse, but the solution is not intuitive. Update attached.

Well, of course it didn't work for you... you changed the delimiter. In Message #1 you said your delimiter was the pipe symbol (which is what Phuocam used in his formula) but in the file you just enclosed, you show the delimiter to be a plus sign. You cannot just change your conditions and expect solutions based on your original conditions to still work. Here is Phuocam's formula adjusted to use your new delimiter...

=TRIM(MID(SUBSTITUTE($A2,"+",REPT(" ",250)),COLUMNS($B$2:B2)*250-249,250))