7 Replies Latest reply on Aug 13, 2019 4:41 PM by John Sarantos

# Split String Number Will Not Convert to Integer

I thought this would be a simple exercise with game scores, but it is proving to be difficult. I am using an Excel sheet as an extract data source. The Score column is a String of two numbers separated by a colon such a 2:3 or 11:4. I split the field into two new fields and they display fine as long as they remain Strings, however when I try to convert them to an integer or a float as either a dimension or measure all I get are Null values. I have tried variations of the calculation below, multiplying by 1, LODs, anything I can think of to no avail. Anyone have a suggestion? Thanks!

• ###### 1. Re: Split String Number Will Not Convert to Integer

Hi Richard,

Any chance you can provide a packaged workbook?

Thank you,

John

• ###### 2. Re: Split String Number Will Not Convert to Integer

Sure. Thanks!

• ###### 3. Re: Split String Number Will Not Convert to Integer

HI Richard,

This fixed it:

Score - Left INT

INT(LEFT(SPLIT([Score],':',1),1))

Score - Right INT

INT(RIGHT(SPLIT([Score],':',2),1))

I did this to figure out what that was that WASN'T a space that could get Trimmed:

ASCII(LEFT(SPLIT([Score],':',2),1))

It's ASCII 160 and Trim doesn't trim it!

1 of 1 people found this helpful
• ###### 4. Re: Split String Number Will Not Convert to Integer

See attached. For some reason the TRIM doesn't appear to be working correctly... if you put LEN([Score - LEFT STR]) you'll see the count of characters is 2, not 1. So basically it's still reading it as a string. I've modified your [Score - Left STR] calculation to be "SPLIT([Score], " : ", 1)". This did the trick.

• ###### 5. Re: Split String Number Will Not Convert to Integer

Thank you John!

I did not know that about ASCII 160.

Cheers!

• ###### 6. Re: Split String Number Will Not Convert to Integer

Thank you Vien!

• ###### 7. Re: Split String Number Will Not Convert to Integer

You're welcome!