Auto detection of number format

[Microsoft_Sourabh123]

I would like to suggest new feature regarding auto detection of number format.

like if I enter $500 it will take into accounting , it makes data feeding and calculating easier for unknown personnel.

[#2]

@Microsoft_Sourabh123 

Format numbers as currency

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011

If you want to display numbers as monetary values, you must format those numbers as currency. To do this, you apply either the Currency or Accounting number format to the cells that you want to format. The number formatting options are available on the Home tab, in the Number group.

For more options and information, please follow the upper link.

...if you have Excel for web

Format numbers as currency in Excel for the web

To show numbers as currency, you can apply either the Currency format or the Accounting format.

(You'll find a comparison of the two formats in the Currency or Accounting section, below.)

1. Select the cells that you want to format and then, in the Number group on the Home tab, click the down arrow in the Number Format box. more infos in the Link.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

Thanks Nikolino DE for your valuable advice. It is a common way to format the number.
But its very difficult to see, feed and format for large dataset and which might hamper valuable business.
But if there is any feature in Excel 2019 which will auto detect formatting like if I type $1 and rupees 1 it will automatically detect the value as monetary value and do related formatting in the backend without writing macro code.
Also there should be a feature that should convert the currency values in the backend and if I do the sum or any Mathematical operations it will give the value as per my required currency.
Example:
I have a dataset containing $ 1,Rupees 1,Rand 1, SGD 1 etc.. I want their sum in SGD So I should get that in Currency conversion tab.
Like that one should develop these features in excel.

[#4]

@Microsoft_Sourabh123 

Here are two suggested solutions with and without VBA.

Solutions Inserted in the file.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#5]

@Microsoft_Sourabh123 

Right place for such suggestions is https://excel.uservoice.com

 If I understood correctly it shall be like

[#6]

@Microsoft_Sourabh123 

That's interesting. If I enter 500 € Excel detects the currency Format.
If I enter 500$ or $500 Excel it's General Format.

Does this belongs to the Regional Settings of Windows.
My settings Regional settings in Windows is German.
Excel is english

[#7]

@German_Chris 

True. If you select another currency symbol in settings, Excel will work such way with it

[#8]

Regional Settings does not affect.

[#9]

@Microsoft_Sourabh123 
I test it on my system:
You have to set the currency symbol in Windows not Excel.
Please see picture below (sorry my Win10 in in German)

1) Open WIN Regional Settings
2) Choose additional Country Settings
3) Choose Region (change Number-Format)
4) Choose extended Settings Button
5) Choos Currency Tab
6) set Currecy Symbol to $


7) Restart Excel (if open)
8) Type in a cell 500$ (not $500)

9) Excel detects the currency

[#10]

@German_Chris 

Cosmetic comment to latest items:

- it's not necessary to reboot Excel

- to put currency symbol in front of number or behind it depends on next regional setting for currency