•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How do I produce a TEXTSPLIT such as in the bottom case in the picture?
Our take
Are you struggling to split a string in Excel or Calc when it lacks hyphens? You’re not alone. Many users encounter challenges when trying to use the TEXTSPLIT function for strings with varying formats. Luckily, there’s a way to make it work smoothly, even when you’re dealing with a single element. In this guide, we’ll explore how to effectively leverage TEXTSPLIT to achieve desired results while returning #N/A errors for unused slots. Let’s dive in and unlock the potential of your data.
Hello, I have only cursory knowledge on Excel/Calc logic. I have a variable cell containing a string containing elements separated by hyphens, which I intend to spill across columns, but it stops working properly (Case 3) when there is only one element since there are no hyphens.
I tried going around it by adding double hyphens to trigger the TEXTSPLIT, but an empty cell is created.
How can I make so that the result returns #N/A errors on unused "slots?" Thank you!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Schrodinger's blank cell. What causes this value to echo differently than others when referenced.https://preview.redd.it/4g124ar0mbjg1.png?width=1209&format=png&auto=webp&s=a77e8cc199e577664fb4da890e2b451896d4f58d d3:e7 are data entered g3 down to g7 is =TEXTSPLIT(TEXTJOIN(",",,D3&","&SUBSTITUTE(E3,",",","&D3&",")),",") m3 is =G3:K8 and as expected, blank cells convert to zeros look at n6 it is not a zero... but-- look at the formulas below as applied to h6 it's not 'blank' (i14/k14) but it is equal to a blank cell (i17/k17) what is it? if I click it- it lights up as a spill cell from the left.. but why didn't it make n6 as a zero? (which is what caught my eye) (edit, and you are all NUTS with your convoluted solutions to https://www.reddit.com/r/excel/comments/1r2aqid/how_to_split_strings_and_join_values/ ) submitted by /u/Way2trivial [link] [comments]
- Can I split columns by number of characters/ digits?I have a list of addresses for a mailing I need to complete. The zip codes column is supposed to read #####-####, but for whatever reason, the hyphen was lost and I'm left with all 9 digits straight. Is there a function I can use to either separate the columns, add the hyphen after the 5th digit, or even just get rid of the last 4 digits altogether? I just need something I can work with for a mail merge, without manually having to change the couple hundred entries. The last 4 digits are nice, but not necessary. I tried using text to columns but since there is no delimitator, I didn't get far. Any advice is appreciated! TIA Excel Version Office 365 version 2603; Build 19822.20114 Excel Environment: desktop, Windows Excel Language English Your Knowledge Level: Formerly intermediate/advanced, but its been almost a decade since it was a daily work task, I'm extremely rusty https://preview.redd.it/urr8yjwwv2sg1.png?width=1186&format=png&auto=webp&s=78e94874c3747545c707380206da8c93931d67b5 submitted by /u/CCCrazyC [link] [comments]
- Previous function that textsplit replacedBefore Textsplit, what function would we use to break out a cell into multiple columns that had unique delimiters? I used to do it all the time, but for the life of me I can not remember what it was. In the function we would have to type everything out like: =?????(b2,{"[ ","] "," ",}) That is just an example. Maybe it was not "splitting" as much as it was taking text from one cell and sourcing it into several columns. Does this ring any bells? submitted by /u/SATprepdropout [link] [comments]
Tagged with
#Excel compatibility#Excel alternatives for data analysis#rows.com#TEXTSPLIT#Excel#Calc#variable cell#string#elements#hyphens#columns#spill#Case 3#double hyphens#empty cell##N/A errors#unused slots#trigger#cursory knowledge#result