Normalizing values in cells with formula in Excel / Google Sheets

Thank you Byundt.

So, I tested a few scenarios with the first formula:
C034  - good
C12 - error (Function UNARY_MINUS parameter 1 expects number values. But C12 is a text and cannot be coerced to a number.)
CC054 - error (same as above)
1 - result = C000
22  - result = C000

I couldn't get the second formula to work.

I am not reproducing your results.

My test data started in cell DD2, so you may need to update the second formula in two places to get it to work.

Greetings Byundt,

Thanks again. I truly owe you again. Although this particular question was already answered for what I needed, I still thank you for providing an alternative formula that I could learn from. :)  

I'm still curious about why your formula fails for me. :puzzled:

But changing your suggestion to:
=TEXT(TEXTJOIN("",,IFERROR(--MID(A2,(LEN(A2)-(ROUND(Len(A2)/3))),3),"")),"\C000")  

Works for all the examples, including an important forgotten example: "C123".

Here is a working sheet:
https://docs.google.com/spreadsheets/d/191QC7RkTOjPpIPWttTFQYHrkYrNZ79x54O7eXbCpIAA/edit?usp=sharing


If you have a moment, I am trying to figure out a solution to this problem:
https://www.experts-exchange.com/questions/29217944/Spreadsheet-Sorting-data-and-include-only-specific-columns.html#questionAdd

Thank you again to both Ron and byundt. I really appreciate your help. :) 

Thank you again.
The formula on your linked Google Sheet worked perfectly.
=ARRAY_CONSTRAIN(ARRAYFORMULA(TEXT(TEXTJOIN("",,IFERROR(--MID(A2,LEN(A2)-{2,1,0},1),"")),"\C000")), 1, 1)

I'm not sure why my alternative formula didn't work for you...
=TEXT(TEXTJOIN("",,IFERROR(--MID(A2,(LEN(A2)-(ROUND(Len(A2)/3))),3),"")),"\C000")
but if you're curious, my sheet will now open for you.

https://docs.google.com/spreadsheets/d/191QC7RkTOjPpIPWttTFQYHrkYrNZ79x54O7eXbCpIAA/edit?usp=sharing

In Excel, the ROUND function takes two parameters: the value to be rounded and the number of decimal places. Both are required. If you omit the value for the number of decimal places--but allow a place for it--the function assumes 0 (whole numbers). So ROUND(5.4, ) returns 5 in Excel. ROUND(5.4) returns an error.

In Google Sheets, the ROUND function takes two parameters, but the second one is optional. If you don't provide the second parameter, Google Sheets assumes no decimal places, so ROUND(5.4) returns 5, the same as ROUND(5.4,) or ROUND(5.4,0).

So your alternative formula requires an additional comma to work in Excel. The first formula below is as you posted (and works in Google Sheets). The second is what I had to do in Excel. The only difference is in ROUND(Len(A2)/3, )

=TEXT(TEXTJOIN("",,IFERROR(--MID(A2,(LEN(A2)-(ROUND(Len(A2)/3))),3),"")),"\C000")  
 
=TEXT(TEXTJOIN("",,IFERROR(--MID(A2,(LEN(A2)-(ROUND(Len(A2)/3, ))),3),"")),"\C000")  

Select allOpen in new window