Problem Combining RIGHT and LEFT Functions Into One Formula

dcparris · April 14, 2024, 5:31pm

I have a cell containing a formula that results in a decimal number (A1 below). I use the RIGHT function in B1 to strip the “0”, and the LEFT function in C1 to leave the first 6 digits after the decimal. I want to combine the two functions from B1 and C1 into a single formula, but am missing something in doing so.

[EDIT]: Formatting B1 to show only 6 digits is not really what I want.

A1 [0.2311545345949]
B1[=IF(LEN(A1)<28,RIGHT(A1,LEN(A1) -1),RIGHT(A1,LEN(A1) - 2))]
*C1 [=IF(LEN(B1)<11,B1,LEFT(B1,LEN(B1) - (LEN(B1)-7)))]

[EDIT]: Desired Result = .231154

What I think the new formula should look like:
=AND(IF(LEN(A1)<28,RIGHT(A1,LEN(A1) -1),RIGHT(A1,LEN(A1) - 2)),IF(LEN(B1)<11,B1,LEFT(B1,LEN(B1) - (LEN(B1)-7))))

Calc adds 4 more right parentheses at the end and gives a ERR:501 error. I’ve lost myself several times in trying to determine the invalid character, or whatever the real issue is.

Thanks in advance.

Thanks!

Villeroy · April 14, 2024, 5:32pm

[strike]Left(A1*10;6)[/strike]

=LEFT(A1*1000000;6)

dcparris · April 14, 2024, 5:40pm

That leaves me with like a 2.3115, whereas I want the .231154.

Villeroy · April 14, 2024, 5:54pm

Well, yes. Try =LEFT(A1*1000000;6) if you get the idea.
Or =ROUNDDOWN(A1*1000000) if you need the number.

mikekaganski · April 14, 2024, 6:03pm

Or =MID(A1;2;7), if the leading dot is important.

dcparris · April 14, 2024, 6:05pm

How can I keep the decimal point, so that it’s part of the result? My RIGHT() function strips only the “0”. My LEFT() leaves me with the .nnnnnn.

Thanks!

dcparris · April 14, 2024, 6:10pm

Thanks Mike! The decimal point is important! I don’t think I knew about MID(), or had overlooked it, but it is much simpler than my own, more convoluted solution!

Lupp · April 14, 2024, 7:41pm

I didn’t find the line where you assured that the content of A1 starts with “0.” not only in the single given example.
If you can assure this, you may consider to use the formula
=TEXT(VALUE(A1);".000000") where I see it as advantages that

in case of a violation of the assured inequality (VALUE(A1)<1) the result will show the error.
the formula tells very clearly what was intended.
the result will apply the common rounding.

dcparris · April 14, 2024, 11:16pm

Thanks. This is also a great solution.

erAck · April 15, 2024, 5:20pm

Note it works only if the current locale’s decimal separator is a . period dot.
For a locale independent version:

=TEXT(NUMBERVALUE(A1;MID(3/2;2;1));MID(3/2;2;1)&"000000")