Formula issue. Rounding number up.

[noveske]

I put a number into F5. The number in F5 is rounded up to the nearest whole number.

Example:
1.1 = 2
9.99 = 10
11.46 = 12

Here is the formula:

=CEILING(LEFT(F5,SUM(LEN(F5)-LEN(SUBSTITUTE(F5,{"0","1","2","3","4","5","6","7","8","9"},"")))),1)

The formula works if the decimal goes out to the hundreths. .xx
But when it's just a single decimal in the tenths, it doesn't round up.

The value that's entered into F5 is: mm (m)

Copied and pasted value. So the formula needs to remove "(m)".

Any solutions here? Been stuck on this one a while.

 

[duggie33]

Could you please provide some sample data? If you could use the XL2BB tool, it would be helpful.

Thanks,

Doug

 

[DanteAmor]

Try:

varios 26sep2023.xlsm
	A					F	G
1
5						1.1 (m)	2
Hoja5

Cell Formulas
Range	Formula
G5	G5	=CEILING(LEFT(F5,SEARCH("(",F5)-2),1)

Or:

varios 26sep2023.xlsm
	A					F	G
1
5						1.123 (m)	2
Hoja5

Cell Formulas
Range	Formula
G5	G5	=CEILING.MATH(LEFT(F5,SEARCH("(",F5)-2),1)

 

[duggie33]

I read it again after reading @DanteAmor response and it make more sense. Another possible option using ROUNDUP....

in cell G5:

=ROUNDUP(LEFT(F5,SEARCH("(",F5)-2),0)

 

[Fluff]

Another option

=CEILING(TEXTBEFORE(F5,"("),1)