Solved

Create a field which has a currency symbol and an amount (with commas)

  • 3 June 2021
  • 6 replies
  • 248 views

Badge +2

Hi all, 

I have a very old SharePoint list which I want to update without breaking any of the existing functionality. 

There are 2 existing fields: 

1. A currency field (which is a choice - £/€) 

2. A string of a number up to 10000000 (without commas) 

 

What I would like to do is concatenate the currency choice and insert commas in to the resulting field. So for example: 

Existing fields : £ and a string of number - e.g. 10000 

What I want : £10,000 in a new field 

 

There is an underlying workflow so editing the fields will kick it off, which I don't want. I have managed to convert 10000 to 10,000 in a few field (using =VALUE([Request Amount])), but ideally I'd like to add in the currency at the start. 

 

Is this possible in SharePoint 2013? 

 

Thanks for you help, apologies I'm a SP newbie. 

icon

Best answer by Nunezma 4 June 2021, 15:54

View original

6 replies

Badge +11

@johns This should be easy enough to accomplish:


 


Add  a calculated column  =CONCATENATE(Currencyfield,TEXT(Amountfield,"#,###"))


 



 

Badge +2
Hi,
Thanks for your reply.
That is almost perfect, the only problem is it isn't converting when the currency field has a 0 in it. Any thoughts?
Badge +11

@johns Can you provide an example? If currency choice field is 0 what should your calculated value be, just the amount?

Badge +2

Sorry - I should have been clearer. If the amount is 0 in the existing string field, I get just a currency sign in the new field. 


I'll try attach a pic. What I want is €0 instead of €. Otherwise, the solution is perfect. Thanks for your help. 


 

Badge +11

@johns Something like this should work.


 


=IF(Amountfield<>"0",CONCATENATE(Currencyfield,TEXT(Amount,"#,###")),CONCATENATE(Currencyfield,("0")))


 



 

Badge +2

Thanks for your help! Marked as solution!

Reply