Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))
Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))
Unless something has changed with the non-subscription version of Excel 2016 (I don't have it, so I don't know), according to Microsoft, TEXTJOIN is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets. So at the moment, that formula is not usable by the majority of Excel users.
Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))
My data has values like
36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.
The following formula worked perfect for me until I got to a set of values that gave me strange answers.
My data has values like
36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.
The problem, as usual, is caused by the tendency of excel to convert everything to date or time value. In this case the problem arises when the first digit in the cell is followed by a space and a "p" or "a". Excel interpretes this situation as 9 pm or 9 am and converts them to time. So the solution (in the framework of the formula above) is avoiding this structure to occur.
My data has values like
36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.
The problem, as usual, is caused by the tendency of excel to convert everything to date or time value. In this case the problem arises when the first digit in the cell is followed by a space and a "p" or "a". Excel interpretes this situation as 9 pm or 9 am and converts them to time. So the solution (in the framework of the formula above) is avoiding this structure to occur.