Forum Discussion
Is number not working
Hi I have a field with the following data
| CNG Rubicon remnant master campaign ongoing |
| 32185184 SANDYSIKE BUILDERS MERCHANTS |
| Simon Huntington Jan - May 16 CN NS 4000pw |
| MF021929-64 Specsavers Q1 30/3-4/7/16 |
| Teads Mediaforce Ongoing Flights |
| Troubleshooting |
| 32308127 THOMAS FORSYTH |
| Amazon Feb 2015 Onwards |
| Magazine Standing House Ad Oct 2015 to Dec 2016 |
| 32211716 Valentines Promo West ad |
| CNG Rubicon remnant master campaign ongoing |
| Kirkgate Comedy up to Feb 19th |
| 32217405 CARTMEL RACECOURSE |
and I am wanting to say if the first 8 numbers isNumber then do something. The problem is it seems to not like it due to it trying to get the value from a text column and is throwing up errors.... Any ideas.
Thanks
C
Anonymous
According to your description, I made a sample for your reference.
I assume you have table called MyTestTable like below.
In this scenario, you can use VALUE() function to determine if the 8 character can be converted into number, then using ISERROR() as condition in IF() statement. Please refer to formula below:
Column2 = IF ( ISERROR ( VALUE ( LEFT ( MyTestTable[Column1], 8 ) ) ), "False", "isNumber" )
Regards
3 Replies
that field is fundamentally a text field - so a simple IsNumber will not work I don't think, and I guess as you have found.
If you have to get all 8 digits then I believe you would need to make a calculated column using LEFT to isolate those values into a new field.... just off the cuff I am guessing you are going to have to put together a SWITCH (or IF) that looks at the 1 position for 0,1,2,3,4,5,6,7,8,9 so that only those get the moved to the new calculated column......because of course you can't have any text in there.....
But maybe that single digit SWITCH check alone could suffice for what you want perhaps - not sure......
I have seen dialog regarding other products that do recast on the fly - but I've never seen it in this product set.... will be interested to see if there is another way...
Anonymous
According to your description, I made a sample for your reference.
I assume you have table called MyTestTable like below.
In this scenario, you can use VALUE() function to determine if the 8 character can be converted into number, then using ISERROR() as condition in IF() statement. Please refer to formula below:
Column2 = IF ( ISERROR ( VALUE ( LEFT ( MyTestTable[Column1], 8 ) ) ), "False", "isNumber" )
Regards
