CODEWITHSUNDEEP
vbaexcel
Amir
Amir

Reputation: 685

How to copy values from two cells and paste in another cell, while preserving the format of both cells?

I have two cells in my excel sheet, one cell contains string such as below:

Gross amount:

while the other cell has currency in euros, for instance:

223.463.687€

Now I want to copy both of these values, i.e. "Gross amount:" and "223.463.687€" and past it into a third cell. The formula that I am using is this:

=(A42&""&B42)

and I get the following result:

Gross Amount: 223463687,150838

But I should have:

Gross Amount: 223.463.687€

NOTE:

The format of currency is in German format, which means that "." (decimal point) and "," (coma) play the opposite roles. In German format, "," is the decimal point where as in English it is ".".

Upvotes: 1

Views: 128

Answers (1)

LocEngineer
LocEngineer

Reputation: 2917

Try this:

=A1 & " " & TEXT(B1;"###.###€")

Edit: Interesting effect! 0 does not get interpreted as "0". Therefore little amendment, German notation (English would be If with , delimiter of course):

=A1 & " " & WENN(TEXT(B1;"###.###")="";"0 €";TEXT(B1;"###.###€"))

Upvotes: 2

Related Questions