# Formatting a number as text & adding a string

Not sure if I should post this here or in Stackoverflow since I'm doing something in VBA, but the formula I want to use is just basic Excel:

Example I have a number in one of my cells: 1741579193,58 (for English speakers it would probably show (1741579193.58)

I want the following format: 1.741.579.193,58 EUR (for English speakers: 1,741,579,193.58 EUR)

In my region the formula looks like this: `TEXT(L33; "#.##0,00;;;")` which should be `TEXT(L33, "#,##0.00;;;")` for anyone in an English speaking country. I get the separators this, but adding "EUR" at the end proves more difficult. Well, I could use `=TEXT(L33; "#.##0,00;;;") & " EUR"`, but can I add the "EUR" directly in the text-formula somehow?

You can use the below to achieve this:

``````=TEXT(A1,"###,###,##0.00")&"EUR"
``````

Or you can apply a custom number format, so when you enter a number like `1741579193.58` into the Cell, it will format as `1,741,579,193.58 EUR`

Right click the Cells you wish to apply it to, and select format cells, then follow the image below

• A nice thing about custom formatting this way, is you can still perform calculations on the cell. Can't do that with the `Text()` formula afaik. Oct 8, 2020 at 13:12
• @BruceWayne I love a good custom number format, confuses the hell outa people who don't know about them tho Oct 8, 2020 at 14:42
• haha yeah exactly. My mind was further blown when I learned you can do a custom number format for positive and negative numbers, AND a zero return. The custom format is vastly underused IMO, makes a great answer :D Oct 8, 2020 at 14:45
• Thank you! I did it with `Text()`, but I believe if I could get the custom format to work it'd be neater. However, I already have the numbers in this format - as strings. Applying the custom format doesn't help in so far as I still can't add them up. Is there a simple way to re-format these strings (in Excel or VBA)? Everything I can think of requires extra steps so that I might as well continue using the `Text()` formula.
– Alex
Oct 9, 2020 at 7:32
• @Alex with custom number format, you can still use the value as a real number, and can add them up Oct 9, 2020 at 7:42

First thing to say is the answer already given is very correct in offering you the way to do it while still allowing the data to remain a number. It is hugely versatile that way while the custom formatting will allow you to render it visually almost however needed.

Second though, and this is a pretty "technically..." kind of thing, you actually can shoehorn the "EUR" part into the actual `TEXT()` function itself, not add it on after the function does its work. What you have to realize is that the format specified between those double quotes is just a string. It has to be a "valid one", to be sure, one that would work directly in the custom formatting feature, but nonetheless, it is just a string. So if you figure how to create it in the custom formatting feature, and make allowance for how some "legal" formatting there will not work in the `TEXT()` function (usually, I believe, due to the difficulty of creating it using string techniques while in the custom formatting feature, you are directly typing so some things Excel can understand that are maybe even impossible to convey to it within the limits of string creation when the resulting string must follow stricter rules than usual).

So, instead of the given, and handier, `space"EUR"`, use the other technique for forcing characters (letters here) into a custom formatting string: `space\E\U\R`:

``````=TEXT(A1,"#,##0.00 \E\U\R")
``````

or even "#,##0.00"`&" \E\U\R"` to ensure you remember you used that little trick two years later when you have to edit the workbook but never used the trick again during those two years...

However, an actual not-a-technicality (but rare) use of the technique could be to take input from some cell in the workbook that the user can edit in order to get the components of the string. For example, perhaps just the "EUR" part. The user enters "EUR" in cell B1 perhaps and you insert the `\` characters so the user doesn't need to enter them or even know they exist:

``````=TEXT(A1,"#,##0.00 "&TEXTJOIN("",FALSE,"\"&MID(B1,SEQUENCE(1,LEN(B1)),1)))
``````

That DOES need `SPILL` functionality, but basically the last portion joins a `\` character to each single character in their entry and the `TEXTJOIN()` stitches that together to add to the formatting string. If you, perhaps, used their entry in another strand of the result shown, the value in your data being acted upon might have been stored as Turkish Lira and you take that knowledge, add their entry which one supposes would stand for "Euro" and convert the lira to euros, and that would be the numerical part of the operation, taking the place of the "A1" I have above.

Other ways to do that, of course, but this puts both halves together relying on the single user entry so...

Finally, back to the fact that the output of `TEXT()` is... well, TEXT. Not numerical, so harder to use later. True, as I agree above, but not "super freaking harder" just "minor-ly harder." After all, you know what text you added to the numerical portion. So a formula like the following can undo that for you:

``````=VALUE(SUBSTITUTE(C1," EUR",""))
``````

(if the result is in C1). Or whatever for the text in the result. Now it's a number again, and can roll forward in its further use as a number not as text. NOT so super hard either, and lets you have the best of all worlds with your chosen mechanism.

That said, more thought in designing the workbook often avoids that kind of back and forth-ing. Worth putting the effort in! But not only do you have to know lots of possibilities (if all you have is a hammer, you have to make the hammer work somehow), there is one BIG other constraint we often have to confront: you are going to take over someone else's work, your time is limited, you need the result NOW, and the last person's work is very Byzantine so you can only get a grip on so much of it in the time you have, much less safely rewrite it all. And related to that, often the users (i.e.: your boss) are VERY used to the exact layout, and the exact input mechanisms, and (less often, lol) have real concerns about being sure your edits did not ruin the integrity of its output. Rightly or wrongly (Often actually the latter in truth. Especially when an author used `IFERROR()` freely...), something that seems to have been right for five years is believed to be valid and error-free. Your changes now... not so much.

So in the real world, you often can only think on the design to fix all the unfortunate work and slowly work it into your edits over time. In the meantime, you need things like the above. No matter how much you'd like to use "proper design."