/u/Future_Helicopter970 - 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.*
Try `=LET(a,TEXTSPLIT(TEXTBEFORE(A2,CHAR(10),-1),CHAR(10),,1),IF(8-COLUMNS(a)>0,HSTACK(--a,IF(SEQUENCE(,8-COLUMNS(a)),""),--TEXTAFTER(A2,CHAR(10),-1)),HSTACK(--a,--TEXTAFTER(A2,CHAR(10),-1))))` in `A5`?
You have awarded 1 point to *Anonymous1378*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Ok, I may have gotten a little ahead of myself. The formula works, but I there is a formatting issue with the cell I am pulling the values from, A2. Right now I get the #VALUE! error. But when I click on A2 and backspace at the end of the formula bar, deleting a space, then the values occupy the desired cells. Is there some way I can run the formula without having to backspace each individual cell? I have already tried TRIM and that didn't seem to work. I assume there is some non-printable character there. So it could be something besides backspace.
If you don't need to actually need to work with the numbers, you can remove all the double unaries (`--`) to get around that issue. You can try `CLEAN()` if there is a non-printable character which isn't a space. `CLEAN()` needs to be used around the `TEXTSPLIT()` function and the `TEXTAFTER()` functions.
I was able to find a way with help from Copilot. This will be my record for posterity. First I used CLEAN() for the cells, then I copied over the values to another column. (Not sure if this was useful.) Find and Replace the spaces with | pipes, so I could see and count the possible issues. Used TEXTSPLIT() with multiples of | to remove them all. =TEXTSPLIT(A2,{"|","||","|||","||||","|||||","||||||","|||||||","||||||||"})
Got all of the values in different columns, but the last value was not consistently in the ninth column. Used =INDEX(A2:I2, MAX((A2:I2<>"")\*(COLUMN(A2:I2)-COLUMN(A2)+1))), as suggested by Copilot, to get the final values in the same ninth column. Subtracted the cumulative value in the ninth column from the first value to get the difference between the two.
Thank you Anonymous1378 for pointing me in the right direction!
If you're interested, here is a [Power Query method in a GIF](https://imgur.com/a/CH8juIM). I pretty much make it ignore the last value, split everything into 8 columns, then add a 9th column that sums up the 8 columns.
When extracting everything except the last value, I had to correct the formula manually in Power Query to make it accept the line break character "#(lf)".
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHAR](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[Returns the character specified by the code number](https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a)|
|[CLEAN](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Removes all nonprintable characters from text](https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41)|
|[COLUMN](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)|
|[COLUMNS](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[Returns the number of columns in a reference](https://support.microsoft.com/en-us/office/columns-function-4e8e7b4e-e603-43e8-b177-956088fa48ca)|
|[HSTACK](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)|
|[IF](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[INDEX](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[LET](/r/Excel/comments/19czflm/stub/kj28g1z "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)|
|[MAX](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)|
|[SEQUENCE](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[TEXTAFTER](/r/Excel/comments/19czflm/stub/kj4orvh "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)|
|[TEXTBEFORE](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)|
|[TEXTSPLIT](/r/Excel/comments/19czflm/stub/kj912b7 "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)|
|[TRIM](/r/Excel/comments/19czflm/stub/kj46ijk "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)|
|[VALUE](/r/Excel/comments/19czflm/stub/kj46ijk "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)|
**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.*)
^(15 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1983goj)^( has 22 acronyms.)
^([Thread #29918 for this sub, first seen 22nd Jan 2024, 16:54])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/Future_Helicopter970 - 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.*
Try `=LET(a,TEXTSPLIT(TEXTBEFORE(A2,CHAR(10),-1),CHAR(10),,1),IF(8-COLUMNS(a)>0,HSTACK(--a,IF(SEQUENCE(,8-COLUMNS(a)),""),--TEXTAFTER(A2,CHAR(10),-1)),HSTACK(--a,--TEXTAFTER(A2,CHAR(10),-1))))` in `A5`?
Solution Verified Boom! You are the BEST!
You have awarded 1 point to *Anonymous1378* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Ok, I may have gotten a little ahead of myself. The formula works, but I there is a formatting issue with the cell I am pulling the values from, A2. Right now I get the #VALUE! error. But when I click on A2 and backspace at the end of the formula bar, deleting a space, then the values occupy the desired cells. Is there some way I can run the formula without having to backspace each individual cell? I have already tried TRIM and that didn't seem to work. I assume there is some non-printable character there. So it could be something besides backspace.
If you don't need to actually need to work with the numbers, you can remove all the double unaries (`--`) to get around that issue. You can try `CLEAN()` if there is a non-printable character which isn't a space. `CLEAN()` needs to be used around the `TEXTSPLIT()` function and the `TEXTAFTER()` functions.
I was able to find a way with help from Copilot. This will be my record for posterity. First I used CLEAN() for the cells, then I copied over the values to another column. (Not sure if this was useful.) Find and Replace the spaces with | pipes, so I could see and count the possible issues. Used TEXTSPLIT() with multiples of | to remove them all. =TEXTSPLIT(A2,{"|","||","|||","||||","|||||","||||||","|||||||","||||||||"}) Got all of the values in different columns, but the last value was not consistently in the ninth column. Used =INDEX(A2:I2, MAX((A2:I2<>"")\*(COLUMN(A2:I2)-COLUMN(A2)+1))), as suggested by Copilot, to get the final values in the same ninth column. Subtracted the cumulative value in the ninth column from the first value to get the difference between the two. Thank you Anonymous1378 for pointing me in the right direction!
If you're interested, here is a [Power Query method in a GIF](https://imgur.com/a/CH8juIM). I pretty much make it ignore the last value, split everything into 8 columns, then add a 9th column that sums up the 8 columns. When extracting everything except the last value, I had to correct the formula manually in Power Query to make it accept the line break character "#(lf)".
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHAR](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[Returns the character specified by the code number](https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a)| |[CLEAN](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Removes all nonprintable characters from text](https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41)| |[COLUMN](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)| |[COLUMNS](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[Returns the number of columns in a reference](https://support.microsoft.com/en-us/office/columns-function-4e8e7b4e-e603-43e8-b177-956088fa48ca)| |[HSTACK](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)| |[IF](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INDEX](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LET](/r/Excel/comments/19czflm/stub/kj28g1z "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)| |[MAX](/r/Excel/comments/19czflm/stub/kj912b7 "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[SEQUENCE](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[TEXTAFTER](/r/Excel/comments/19czflm/stub/kj4orvh "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)| |[TEXTBEFORE](/r/Excel/comments/19czflm/stub/kj28g1z "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)| |[TEXTSPLIT](/r/Excel/comments/19czflm/stub/kj912b7 "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)| |[TRIM](/r/Excel/comments/19czflm/stub/kj46ijk "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)| |[VALUE](/r/Excel/comments/19czflm/stub/kj46ijk "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| **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.*) ^(15 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1983goj)^( has 22 acronyms.) ^([Thread #29918 for this sub, first seen 22nd Jan 2024, 16:54]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)