Input Tool adding additional decimals to .xlsx file

[Matt_Kerr]

When I import the attached .xlsx file I get two different values if the column starts with a string or number. I need for the input to show below. Is there a way to get Alteryx to bring in it all as string and not get all of the additional decimal places? 

String and Double

String

10200.2

[DiganP]

@Matt_Kerr You want to use this function in the formula tool. Make sure the data type is double.

ToNumber([String and Double]).

This will give you the numbers as a double while turning text into 0. Is this what you were looking for?

[Matt_Kerr]

@DiganP  Unfortunately not. I need it to show the string for the first row, then 10200.2 for the 2nd row.

[BrandonB]

Will there ever be numbers in the string value that you want to return? If not, you could use REGEX_Match() to check for a digit and a conditional formula that converts the value to a number if so or doesn’t if not.

[DiganP]

@Matt_Kerr Attached is the workflow with the logic you might use. You can use the tonumber function first then the conditional statement. 

if [Test]=0 then [String and Double] else [Test] endif

[Matt_Kerr]

I actually have some other data within that column that I need to stay as is but unfortunately those formulas change the data

I have attached the Excel file. I need need the first column to come in as a string in alteryx (looking like it does in excel).

[Matt_Kerr · Accepted solution]

I was able to resolve it with the following formula:

if REGEX_Match([String and Double],"\d+\.\d+")
then tonumber([String and Double])
else [String and Double]
endif

Thanks,

[aeking]

I had a similar issue with Alteryx adding extra decimal spaces, but after applying this REGEX formula I noticed a new issue. Some items in my list have leading or ending zeros and a decimal place. So '0123.678' is becoming '123.678' and '385.3900' is becoming '385.39'. 

Is there a way to correct Alteryx creating the extra decimal spaces while also keeping leading and ending zeros where they exist?

[BrandonB]

@aeking numbers by definition cannot have leading zeroes and trailing zeroes are additional precision placeholders. If you need these leading and trailing zeroes you should likely keep your values as strings rather than converting to numbers. 

[vaughangary]

What about 03 as text into Excel?  If you type it directly into Excel, it is '03 (showing as 03).  From alteryx, i only get 03 from 03 or '03 from '03.  Neither replicate the Excel scenario where '03 shows as 03.