Is number not working

[Anonymous]

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

[v-ljerr-msft · Accepted solution]

@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

[Anonymous]

Working 🙂

[CahabaData]

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...