asked on # Normalizing values in cells with formula in Excel / Google Sheets

Hello all,

I thought this be trivial but I'm just not finding the right solution.

I have a column of cell entries where there are occasion input errors for the non-numerical portion of the values. So, I want to automatically correct these errors.

Examples:

Input value / Corrected input value)

C005 / C005 (already correct)

CT021 / C021

044 / C044

33 / C033

In other words, the correct value is always going to be in the format, "Cxxx". Where xxx = 000 - 999. The leading zeros are important.

Thank you

I thought this be trivial but I'm just not finding the right solution.

I have a column of cell entries where there are occasion input errors for the non-numerical portion of the values. So, I want to automatically correct these errors.

Examples:

Input value / Corrected input value)

C005 / C005 (already correct)

CT021 / C021

044 / C044

33 / C033

In other words, the correct value is always going to be in the format, "Cxxx". Where xxx = 000 - 999. The leading zeros are important.

Thank you

* Google SuiteGoogle* google spreadsheetsMicrosoft ExcelMicrosoft Office

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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.

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

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. :)

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

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, )

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")
```

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.