is there a function in Excel to apply the below logic-CodePudding

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

CodePudding user response：

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