Convert text to number

macfuller

Active Member
Joined
Apr 30, 2014
Messages
272
Office Version
365
Platform
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?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
647
Office Version
365, 2013
Platform
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,600
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top