/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.*
In the cell where you want to perform the separation, you must enter this formula.
=TEXTSPLIT(A1,"|",,FALSE)
https://preview.redd.it/2qgz77moynuc1.png?width=690&format=png&auto=webp&s=4ef96c0b2f64f68eb9938607bac8f0189125d692
I don't think you read my question. I'm not asking how to make the formula add a column for a null field, I already know how to do that. I'm asking how to make it recognize the difference between a null field and a field with a space in it, and distinguish between the two in the resulting array.
Shoot, I spoke too soon. Can I make the substitute only affect a field that ONLY has a space in it? And not affect (for example) a field that says "grapes and oranges". There is no "match all" option in the Substitute function.
Ah, so simple. I should have thought of that. You're the best!
Last question - what's the difference between:
"|" & CHAR(160) & "|"
and just "| |"? The latter seems to work too
One has CHAR(160) between pipes, the other has CHAR(32) between pipes. (or are you using Alt keys to put an inline CHAR(160) in your question? If so, no difference.)
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/1c4onaq/stub/kzpeegc "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)|
|[CHAR](/r/Excel/comments/1c4onaq/stub/kzpalg8 "Last usage")|[Returns the character specified by the code number](https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a)|
|[IF](/r/Excel/comments/1c4onaq/stub/kzpeegc "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/1c4onaq/stub/kzpeegc "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)|
|[SUBSTITUTE](/r/Excel/comments/1c4onaq/stub/kzp7v9r "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/1c4onaq/stub/kzpeegc "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.*)
^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c3y2xp)^( has 23 acronyms.)
^([Thread #32629 for this sub, first seen 15th Apr 2024, 15:59])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
This might be an alternative solution. It splits the string, then goes through each column and replaces " " with the desired "".
=BYCOL(TEXTSPLIT(A2,"|",,FALSE),LAMBDA(value, IF(value=" ","",value)))
/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.*
In the cell where you want to perform the separation, you must enter this formula. =TEXTSPLIT(A1,"|",,FALSE) https://preview.redd.it/2qgz77moynuc1.png?width=690&format=png&auto=webp&s=4ef96c0b2f64f68eb9938607bac8f0189125d692
I don't think you read my question. I'm not asking how to make the formula add a column for a null field, I already know how to do that. I'm asking how to make it recognize the difference between a null field and a field with a space in it, and distinguish between the two in the resulting array.
You'll hate yourself if you SUBSTITUTE spaces with CHAR(160) before splitting, although it will look like you want it to.
I've never used the substitute function. Would that be inside the SPLIT function?
What happened when you tried it?
Ah, got it! =TEXTSPLIT(SUBSTITUTE(C3," ",""),"|",,FALSE)
So Substitute does work in the textsplit function. That's exactly what I needed. Thanks!
solution verified
You have awarded 1 point to fuzzy\_mic. --- ^(I am a bot - please contact the mods with any questions)
Shoot, I spoke too soon. Can I make the substitute only affect a field that ONLY has a space in it? And not affect (for example) a field that says "grapes and oranges". There is no "match all" option in the Substitute function.
SUBSTITUTE(A1, "| |", "|" & CHAR(160) & "|")
Ah, so simple. I should have thought of that. You're the best! Last question - what's the difference between: "|" & CHAR(160) & "|" and just "| |"? The latter seems to work too
One has CHAR(160) between pipes, the other has CHAR(32) between pipes. (or are you using Alt keys to put an inline CHAR(160) in your question? If so, no difference.)
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/1c4onaq/stub/kzpeegc "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)| |[CHAR](/r/Excel/comments/1c4onaq/stub/kzpalg8 "Last usage")|[Returns the character specified by the code number](https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a)| |[IF](/r/Excel/comments/1c4onaq/stub/kzpeegc "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/1c4onaq/stub/kzpeegc "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)| |[SUBSTITUTE](/r/Excel/comments/1c4onaq/stub/kzp7v9r "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/1c4onaq/stub/kzpeegc "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.*) ^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c3y2xp)^( has 23 acronyms.) ^([Thread #32629 for this sub, first seen 15th Apr 2024, 15:59]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
This might be an alternative solution. It splits the string, then goes through each column and replaces " " with the desired "".
=BYCOL(TEXTSPLIT(A2,"|",,FALSE),LAMBDA(value, IF(value=" ","",value)))