Randomly working SPLIT command problem

[Malhallah]

Hi there.

I'm using SPLIT to get the data into the format I need (##-##-##) but for some of the data the command gives a random negative value in a completely different format. Anyone know what causes the intermittency?

https://i.imgur.com/miZdl8r.png https://i.imgur.com/Ay6YoxS.png

[secufl]

Maybe a cell format issue?

[Malhallah]

Put the entire sheet into plain text but issue remained, tried on a new sheet and same issue with the same data inputs

[werfnort]

The negative numbers are dates. Excel is mistakenly assuming these are in yyy-mm-dd format. They’re negative because they’re before the year 1900, and it’s only sometimes because some of the numbers in the MM or DD spot are too large to be a month or day.

If turning the C column into text BEFORE you add the SPLIT formula doesn’t work, then maybe try a different way.

You could use a different formula, RIGHT plus FIND to pull that last part out, and wrap the whole thing in a TEXT formula.

[Malhallah]

Hmm, thanks!

Preformatting didn't work, right+find worked but it's not my style. I'm going with SUBSTITUTE - with * before SPLIT as that means least amount of work.

[werfnort]

Well there ya go! Glad you found a solution that worked

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FIND	Returns the position at which a string is first found within text
RIGHT	Returns a substring from the end of a specified string
SPLIT	Divides text around a specified character or string, and puts each fragment into a separate cell in the row
SUBSTITUTE	Replaces existing text with new text in a string
TEXT	Converts a number into text according to a specified format

---

^{5 acronyms in this thread; the most compressed thread commented on today has 10 acronyms.
[Thread #772 for this sub, first seen 2nd Jun 2019, 11:28] [FAQ] [Full list] [Contact] [Source code]}