Convert text to number

macfuller

Active Member
Joined
Apr 30, 2014
Messages
298
Office Version
  1. 365
Platform
  1. Windows
I'm thinking this should be simple to identify if a character is a number, but I had to put in an odd workaround.

I have one calculated column that pulls in a project number to the Match Exception table from the Voucher table.
VBA Code:
[PO Project]=
CALCULATE (
    FIRSTNONBLANK (
        Vouchers[Project],
        1
    ),
    FILTER (
        Vouchers,
        Vouchers[PO Number] = 'Match Exception'[PO No.]
    )
)
Not all Vouchers have project numbers, and different lines on a voucher may have different projects. Because the Match Exception file does not have the same granularity (e.g. line level data) as the Voucher table, I use the FIRSTNONBLANK construction. It's close enough as we won't mix capital and grant POs.

So I want another column to tell if the project is capital or a grant so I can put it into a slicer for users to select. We use the project field for multiple purposes (bad design, pre-dates me) so we can have capital project IDs or Federal grant IDs. Capital projects start with a number [17COMWB0123], grants with a letter [A43300]. Easy enough I would have thought to use
IF ( ISNUMBER(LEFT('Match Exception'[PO Project],1)), ...

But no, that always returns FALSE. So I tried
IF ( ISNUMBER(VALUE(LEFT('Match Exception'[PO Project],1))), ...

But no, that returns an error because VALUE returns a variant. So this works, but it seems horrible.
VBA Code:
=
IF (
    'Match Exception'[PO Project] = "",
    BLANK (),
    IF (
        LEFT('Match Exception'[PO Project],1) IN 
           {"0","1","2","3","4","5","6","7","8","9"},
           "Capital",
           "Grant"
             )
)

I must be missing something obvious?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hi,

I agree that the functionality is a little bit confusing, as your original approach would work perfectly fine in Excel. The unfortunate feature of DAX columns is that a given function is being applied to the entire column, so - even if one row returns an error - the whole column is flagged with an error. I think your function is quite nice, you might want to consider using some alternative, like checking the UNICODE number for the first character -

Rich (BB code):
Check = 
    var code = UNICODE(LEFT('Match Exception'[PO Project], 1))
return
    IF(
        code,
        IF(AND(code >= 48, code <= 57), "number", "text")
    )

1596192275549.png


Alternatively, what you were trying to do would (almost) work fine in DAX measure - just a small tweak to the formula:

1596192447570.png
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,133,429
Messages
5,658,740
Members
418,467
Latest member
sc356448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top