Converting scientific notation within a string field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Binaucs - of course! End of a long day and I'd missed the obvious! Thank you.
![](/skins/images/1AD6617C767659D8A9F9801BEDFCFB20/responsive_peak/images/icon_anonymous_message.png)