'is there a function in Excel to apply the below logic

if the column value is 1, I need to print 1.0000 and

if the column value is 1.1 , i need to print 1.0001

if 1.10 -> 1.0010

if 1.100 -> 1.0100

I am trying to think with regex, is there a way to code like this in excel?

if column has ([0-9].[0-9]{1}) -> add 3 zeros after decimal

if column has ([0-9].[0-9]{3}) -> add 1 zero after decimal

Thanks

excelregexfunctionif-statement


Solution 1:[1]

You didn't provide enough details about your problem, but I tried to solve it as I understood it. Firstly, you have to store your inputs as TEXT since 1.1 is equal to 1.10, 1.100, 1.1000 Then you can use the following formula to get the required outputs

=IF(LEN(A1)=3,CONCATENATE(LEFT(A1,2),"000",RIGHT(A1,1)),
IF(LEN(A1)=4,CONCATENATE(LEFT(A1,2),"00",RIGHT(A1,2)),
IF(LEN(A1)=5,CONCATENATE(LEFT(A1,2),"0",RIGHT(A1,3)),
IF(LEN(A1)=6,CONCATENATE(LEFT(A1,2),"",RIGHT(A1,4)),""))))

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions