Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Extracting a number from Text

Hi everyone :), I have a column with texts such as " XXXX P10000 7FFFF" , "1BCD R150000" would you please help me to find a formula to extract the 10000 and 150000 after the P and R. They are always either P or R as the begining. Thanks alot.
Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago
    Column = 
    VAR __P = FIND("P",[Column1],,BLANK())
    VAR __R = FIND("R",[Column1],,BLANK())
    VAR __Start = IF(ISBLANK(__P),__R,__P) + 1
    VAR __End = FIND(" ",[Column1],__Start,LEN([Column1]))
RETURN
    MID([Column1],__Start,__End - __Start)

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Column = 
    VAR __P = FIND("P",[Column1],,BLANK())
    VAR __R = FIND("R",[Column1],,BLANK())
    VAR __Start = IF(ISBLANK(__P),__R,__P) + 1
    VAR __End = FIND(" ",[Column1],__Start,LEN([Column1]))
RETURN
    MID([Column1],__Start,__End - __Start)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Please do not do this in DAX as it's not a language to shape data and its power to analyze strings is very limited. Such things belong to the data preparation phase and should be performed in Power Query or in the data source.

    DAX is a Data Analysis eXpressions language. Not a data mashup engine.

    Best
    D