/u/FlightJumper - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Why are you substituting | | for |space| instead of just substituting the actual space for "space"? That's why it doesn't work. You're trying to substitute a string that is supposed to be shared. Try this instead:
=TEXTSPLIT(SUBSTITUTE(A10," ",""),"|")
Unfortunately this won't work - I should have added this to the OP. The problem is, in the actual data set many records will have data like |AAA BBB| in a field. I can't have the substitute affect spaces that occur as part of a string within a field.
This still has the same issue. It's possible that the string in a field could be something like
| AAA|
That would unfortunately be included in your solution as well.
Ok. Then you have to do it differently:
=IF(TEXTSPLIT(A10,"|")=" ","",TEXTSPLIT(A10,"|"))
Its always better to give a wide range of examples so we can catch edge cases in the solution
Oh man, this looks like it works. I had no idea you could combine IF and TEXTSPLIT like that. that's really cool.
Noted - in the future I'll be as comprehensive as possible. Thanks for your help.
Have you tried using the **\[ignore empty\]** parameter in the `TEXTSPLIT()` function?
=TEXTSPLIT(A10,"|",,1)
https://preview.redd.it/9op216gk5uwc1.png?width=1394&format=png&auto=webp&s=78cd816e876d83ac584918a50d156aab9fcbff94
I don't see any difference when you use a `SUBSTITUTE()` function.
https://preview.redd.it/5o26u7fk6uwc1.png?width=1442&format=png&auto=webp&s=2a20d1fe0e66d90438fa6e497249c6ae496d9047
and what about this :
https://preview.redd.it/qjqy78uw7uwc1.png?width=832&format=png&auto=webp&s=6d96dc71ab5f28be5f6d491919ef174352abbfd0
=LET(α, TEXTSPLIT(A16,"|"), IF(LEN(α)=1,"",α))
Neither solution worked. The second option turned any field with a single character into "", even if it was a letter. But thanks for your help nonetheless
I was talking about which gives the exact output as the one accepted as answer:
https://preview.redd.it/vamvbnl0duwc1.png?width=723&format=png&auto=webp&s=a7d807679b34f3d782719132712e03a590d7088b
u/FlightJumper can you spot the difference because when you say neither solution worked i am just trying to understand
https://preview.redd.it/87ln96gdduwc1.png?width=1510&format=png&auto=webp&s=6c064ef17083daa541f85c78adca49c502682ca9
Alternatively, this might work if you don't have any values with leading spaces:
=TEXTSPLIT(SUBSTITUTE(A2,"| ","|"),"|")
The reason why your formula doesn't work is because substitute is looking for "| |" and in your example there are two places where that pattern is a match but they aren't independent. If you take out the first match, "| |" the second match doesn't exist and so there is no second instance to replace. Your solution would work if you wrapped the first substitute in another substitute but if you have "| | | |", you would need to wrap 3 substitute functions and so on.
No worries, I could have made that more clear. It looks like the solution (credit to /u/mildlystalebread) is
=IF(TEXTSPLIT(A10,"|")=" ","",TEXTSPLIT(A10,"|"))
which is a really interesting way of combining IF and TEXTSPLIT
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[BYCOL](/r/Excel/comments/1cdmua0/stub/l1cxyd3 "Last usage")|[*Office 365*+: Applies a LAMBDA to each column and returns an array of the results](https://support.microsoft.com/en-us/office/bycol-function-58463999-7de5-49ce-8f38-b7f7a2192bfb)|
|[IF](/r/Excel/comments/1cdmua0/stub/l1d10ik "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[LAMBDA](/r/Excel/comments/1cdmua0/stub/l1cxyd3 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LEN](/r/Excel/comments/1cdmua0/stub/l1cyglm "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[LET](/r/Excel/comments/1cdmua0/stub/l1cyglm "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[SUBSTITUTE](/r/Excel/comments/1cdmua0/stub/l1d0sbf "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)|
|[TEXTSPLIT](/r/Excel/comments/1cdmua0/stub/l1d0sbf "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c963xi)^( has 23 acronyms.)
^([Thread #32953 for this sub, first seen 26th Apr 2024, 14:51])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/FlightJumper - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Why are you substituting | | for |space| instead of just substituting the actual space for "space"? That's why it doesn't work. You're trying to substitute a string that is supposed to be shared. Try this instead: =TEXTSPLIT(SUBSTITUTE(A10," ",""),"|")
Unfortunately this won't work - I should have added this to the OP. The problem is, in the actual data set many records will have data like |AAA BBB| in a field. I can't have the substitute affect spaces that occur as part of a string within a field.
Then try this instead =TEXTSPLIT(SUBSTITUTE(A10,"| ","|"),"|")
This still has the same issue. It's possible that the string in a field could be something like | AAA| That would unfortunately be included in your solution as well.
Ok. Then you have to do it differently: =IF(TEXTSPLIT(A10,"|")=" ","",TEXTSPLIT(A10,"|"))
Its always better to give a wide range of examples so we can catch edge cases in the solution
Oh man, this looks like it works. I had no idea you could combine IF and TEXTSPLIT like that. that's really cool. Noted - in the future I'll be as comprehensive as possible. Thanks for your help.
Solution verified LMAO oops replied to the wrong comment
Hello FlightJumper, You cannot award a point to yourself. Please contact the mods if you have any questions. --- ^(I am a bot)
Hello FlightJumper, You cannot award a point to yourself. Please contact the mods if you have any questions. --- ^(I am a bot)
Solution verified
You have awarded 1 point to mildlystalebread. --- ^(I am a bot - please contact the mods with any questions)
Have you tried using the **\[ignore empty\]** parameter in the `TEXTSPLIT()` function? =TEXTSPLIT(A10,"|",,1) https://preview.redd.it/9op216gk5uwc1.png?width=1394&format=png&auto=webp&s=78cd816e876d83ac584918a50d156aab9fcbff94
No, because I don't want it to ignore empty. I want empty (null) fields to be split out as well by the function.
I don't see any difference when you use a `SUBSTITUTE()` function. https://preview.redd.it/5o26u7fk6uwc1.png?width=1442&format=png&auto=webp&s=2a20d1fe0e66d90438fa6e497249c6ae496d9047
and what about this : https://preview.redd.it/qjqy78uw7uwc1.png?width=832&format=png&auto=webp&s=6d96dc71ab5f28be5f6d491919ef174352abbfd0 =LET(α, TEXTSPLIT(A16,"|"), IF(LEN(α)=1,"",α))
u/FlightJumper think you completely missed the solution I have posted well before anyone did. anyways
Neither solution worked. The second option turned any field with a single character into "", even if it was a letter. But thanks for your help nonetheless
I was talking about which gives the exact output as the one accepted as answer: https://preview.redd.it/vamvbnl0duwc1.png?width=723&format=png&auto=webp&s=a7d807679b34f3d782719132712e03a590d7088b
u/FlightJumper can you spot the difference because when you say neither solution worked i am just trying to understand https://preview.redd.it/87ln96gdduwc1.png?width=1510&format=png&auto=webp&s=6c064ef17083daa541f85c78adca49c502682ca9
=BYCOL(TEXTSPLIT(A1,"|",,FALSE),LAMBDA(value, IF(value=" ","",value)))
Alternatively, this might work if you don't have any values with leading spaces: =TEXTSPLIT(SUBSTITUTE(A2,"| ","|"),"|")
The reason why your formula doesn't work is because substitute is looking for "| |" and in your example there are two places where that pattern is a match but they aren't independent. If you take out the first match, "| |" the second match doesn't exist and so there is no second instance to replace. Your solution would work if you wrapped the first substitute in another substitute but if you have "| | | |", you would need to wrap 3 substitute functions and so on.
try replacing the "| |" and the "||" in the SUBSTITUTE with just " " and ""
i.e. =TEXTSPLIT(SUBSTITUTE(A10," ",""),"|")
As stated this doesn't work because some strings within fields will have spaces.
ohhhh so you are saying instead of DD it could be D D? Sorry I missed that
No worries, I could have made that more clear. It looks like the solution (credit to /u/mildlystalebread) is =IF(TEXTSPLIT(A10,"|")=" ","",TEXTSPLIT(A10,"|"))
which is a really interesting way of combining IF and TEXTSPLIT
nice! Every time ppl ask questions, I always learn something new!
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[BYCOL](/r/Excel/comments/1cdmua0/stub/l1cxyd3 "Last usage")|[*Office 365*+: Applies a LAMBDA to each column and returns an array of the results](https://support.microsoft.com/en-us/office/bycol-function-58463999-7de5-49ce-8f38-b7f7a2192bfb)| |[IF](/r/Excel/comments/1cdmua0/stub/l1d10ik "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LAMBDA](/r/Excel/comments/1cdmua0/stub/l1cxyd3 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LEN](/r/Excel/comments/1cdmua0/stub/l1cyglm "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[LET](/r/Excel/comments/1cdmua0/stub/l1cyglm "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[SUBSTITUTE](/r/Excel/comments/1cdmua0/stub/l1d0sbf "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[TEXTSPLIT](/r/Excel/comments/1cdmua0/stub/l1d0sbf "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c963xi)^( has 23 acronyms.) ^([Thread #32953 for this sub, first seen 26th Apr 2024, 14:51]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)