Alteryx automatically converts string (£45.50) to number (45.50) while importing file?

[anks_joi]

Hello people!

I am importing a .xlm file that has value column with currency eg. £45.50 or 56.70 EUR. When I import this file using Input Tool, Alteryx automatically converts these values into numbers and removes the £ sign and EUR. I would like to keep it as is and don't want it Alteryx to convert values to numbers because I need to see which values are £ and which are EUR. Can someone please help with this query?

Thanks in advance!

[vizAlter]

Hi @anks_joi — Can you attach sample data?

(Looks that digits are not in String data type in your input file.)

[anks_joi]

@vizAlter - Thank you for your response. Unfortunately I can't attach data as it is confidential client data. I think you are right, the digits are not in string data type it is "Custom". I have 52 such excel files with half a million rows of data in each file. Is there a way I can convert the number to string in Alteryx and still have £ and EUR in the column?

[vizAlter]

@anks_joi — Okay, then once you bring the data to Alteryx workflow and use a Formula to put the Currency sign or abbreviations.

for example to create a new field or update the [Amount] filed:   "$"+[Amount]

If the layout of your each file is the same, then you can bring all of them in one shot, like this screenshot:

If it resolves your query please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution.  Thank you.

[anks_joi]

@vizAlter - My column in excel has £ and EUR both in same column hence I can't have just one currency by using Filter tool. I would like to bring in the data as it is, so that I can convert EUR to £ and have everything as £ in one column. Below is the column in my .xlm file, for eg.:

Value Retail Loss

-£ 21.20
£ 1,092.50
£ 1,134.00
-£ 21.20
-21.00 EUR
-21.00 EUR
-20.99 EUR
-20.98 EUR

[vizAlter]

@anks_joi — Let me know that what is mentioned for "Custom" format for your Currency column, provide that too.

[vizAlter · Accepted solution]

@anks_joi  —  Try this solution..., hope it will be helpful, if not let me know.

In Formula tool:

"£ "+ REGEX_Replace([Value Retail Loss], "([^0-9,.-])", "")

Please mark it "Solved" or "Solved" with a Like if it resolved your query. This will help other users find the same answer/resolution.  Thank you.