Converting scientific notation within a string field

[spettifor]

I'm working on a dataset which contains ISBN numbers in a V_WString field. Some are in the correct full format but some are expressed in scientific notation, eg "9.785174E+12" or sometimes "9.78515 e+12".

I've been able to deal with this by creating a new field [ISBNISSN temp] and then removing the original [ISBNISSN] and renaming the new one. In this new field I've used an IF THEN ELSE statement to do this - see below -

IF(REGEX_Match([ISBNISSN],'(\d+(\.\d+)?)[eE]([+-]?\d+)'))
THEN ToNumber(ISBNISSN)
ELSE [ISBNISSN]
ENDIF

...but am wondering if I can get the same result by running the formula within the existing [ISBNISSN] field. I've tried with various iterations of RegEX_Replace but I'm not getting the right output and am wondering if the V_WString format of the field is the issue (I'd need to keep this as is as there are genuine strings in the data that I'd need to preserve). Any thoughts?

[binuacs · Accepted solution]

@spettifor try the below formula on the same field using a formula tool

IF(REGEX_Match([ISBNISSN],'(\d+(\.\d+)?)[eE]([+-]?\d+)'))
THEN toString(ToNumber(ISBNISSN))
ELSE [ISBNISSN]
ENDIF

[danilang]

Hi @spettifor 

You might want to examine the ISBN number in the source.  If the value is in scientific notation, you're only seeing the first 8 or so digits of the number and since 2007, isbn number have 13 digits, so you'll be missing the last 5 digits of the number. 

If your data is in excel, format the columns as string to avoid the scientific notation in the first place.

Dan  

[spettifor]

Thanks Dan, I'd not picked up on the ISBN point, just accepting that the data was as is (mistake!). I've traced this back to the source system and the data output process which puts it into scientific notation, truncating the ISBN with zeroes, straight into the csv as text (ie "9.354234E+12"). So we need to go back to that system and look at how it exports the data in its full form. We may not have spotted this without you noticing!

Simon

[spettifor]

Binaucs - of course! End of a long day and I'd missed the obvious! Thank you.