Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

VBA: Insert New column with specific format type

Hello experts,

I am launching a comparison beween the two columns

User generated image
The problem is that they don't have the same format.

I would like to insert a new column related to Field2, Field2 revised in Column C in order to match exactly the format reported in Column A:
-Value should aways end with €/h
-Values should reported with a decimal separator and 2 decimal places

I attached dummy file.

Thank you very much for your help.
Book5.xlsm
Avatar of als315
als315
Flag of Russian Federation image

You can use this formula:
=SUBSTITUTE(TEXT(B2,"#.00")& " €/h",".",",")
Book5.xlsm
Avatar of Luis Diaz

ASKER

Thank you for your help.

The formula works perfectly just for the following value:
0.00 I have in result ,01 €/h instead of having 0,01 €/h
If I want to use the same formula to have the following result:
12 041,70 €/m based on the following value 12041.7
or 15 207,47 €/m based on the following value 15207.46667
=TRIM(SUBSTITUTE(TEXT(B2,"### ### ### ##0.00")& RIGHT(A2,4),".",","))
Look at new sample
Book5.xlsm
Thank you very much and for the following:

12 041,70 €/m based on the following value 12041.7
or 15 207,47 €/m based on the following value 15207.46667
€/m or €/h is now taken from first column. Correct value in first column to 12 041,70 €/m
Thank you very much it works, however in the formula we take as a reference at the same time b column and a column. We cannot just param a formula in which I putin column A 12041.7 and have as a result 12 041,70 €/m?

Thank you very much for your help.
Can you upload new sample?
Please find attached the sample.

Output is the expected result.

Thank you very much for your help.
Book2.xlsx
How we can decide what to add: €/m or €/h?
For this specific case €/m as I have the previous formula which works.
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial