How to concatenate numbers with 2 fixed decimals

Hey guys,

I just read this thread because I have to concatenate a lot of cells, containing both numbers and text. I need the numbers to be displayed with 2 decimals in the cell where they are being concatenated.

In the thread above it was said that it's possible with the FIXED fuction. But since I have a lot of cells, I was wondering if it is possible to use some kind of trick to make them with 2 fixed decimals. Or do I have to add "FIXED" before every cell value?

=CONCATENATE(AR84;B84;AS84;AW84;AX84;AY84;AZ84;BF84;BG84;BH84;BI84;BJ84;BK84;BL84;BM84;BN84;BO84;BP84;BU84;BZ84;CA84;CB84;CC84;CD84;CE84;CF84;CG84;CH84;CI84;CJ84;CO84;CT84;CU84;CV84;CW84;CX84;CY84;CZ84;DA84;DB84;DC84;DD84;DI84;DN84;DO84;DP84;DQ84;DR84;DS84;DT84;DU84;DV84;DW84;DX84;EC84;EH84;EI84;EJ84;EK84;EL84;EM84;EN84;EO84;EP84;EQ84;ER84;EW84)

I am not a very experienced Excel user. Hope you can help me out here.

Thanks.

Best regards,
Bas

Hello Neil_,

First of all: thanks for the quick answer!
I tried this, but it does not work. The cel in which all the data comes together still has value's like 0.6 instead of 0.60. While the cell which contains the value 0.60 is displaying 0.60 and not 0.6.

What can be the problem?

Best regards,
Bas

I don't know what the purpose would be to join (concatenate) all those numbers together without some kind of separator between them to enable you to distinguish them apart - you will just end up with a string of digits and decimal points (which for you I would assume would be commas) like this:

10,0211,234,56789,00

and so on. What's the point in this?

Pete

Hello Pete,

I also have text, like I mentioned, but you probably missed that part. Actually it contains HTML, text and numbers. So the final concatenated cell is a piece of HTML code, containing text and prices in a table, which I later on will upload via a CSV file in a webshop system.

But that it actually not important information, because the main point now is that it doesn't work!

I hope someone can help me.

Thanks,
Bas

You would have to apply the Text function to each numeric cell in the concatenation..

Sorry, I'm not going to go through all of them..but something like
=CONCATENATE(AR84;TEXT(B84;"0.00");AS84;TEXT(AW84;"0.00");AX84)

Where B84 and AW84 are numeric cells, AR84 AS84 and AX84 are Text.

your right about the commas btw.

Hello Neil,

Yes, I have more then 300 characters. Any other options?

Best regards,
Bas

So, you don't want every cell reference in that formula to have FIXED( ... ,2) around it?

Another way of doing it would be to use the TEXT function, i.e.:

TEXT(B84,"0.00")

(this is in English, but you will probably have to use a semicolon instead of the comma and a comma instead of the full stop), but if you only need to do this to selected cells then you could adapt the following procedure:

put an apostrophe ( ' ) before the = sign in the cell with the formula, then put

TEXT(

(or the Dutch equivalent) in another cell. Click on the formula bar as if to edit the cell, highlight the contents and do CTRL-C to copy it, then press the < Esc > key. Then click on the cell with the CONCATENATE formula in it and click on the formula bar and then move the cursor to just in front of the cell reference for a number, and then do CTRL-V to paste the TEXT( characters in front of the cell reference. Continue to do this for the other cell references which contain numbers.

Then you can repeat this sequence by putting the second part of the function in the temporary cell, i.e.:

;"0,00")

and then copy this and press < Esc > again, then paste it after those cell references that you have already amended.

Finally, you can delete the apostrophe from in front of the = sign.

Hope this helps.

Pete

Hi Pete, I will try this tomorrow first thing in the morning and will let you know if it worked!

Thanks!

Hi Pete,

Eventually I used your copy paste strategy with the normal fixed function. Its the shortest way, because FIXED( is using two decimals by default. Thanks for the help!

Best regards,
Bas

Glad to hear that you got it sorted in the end.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete