Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Extract numbers&text

Hi team,
I have a column with different type ot data in it- only numbers, numbers&text, only text.  This is a short example:

Subject
7A
14C
12B SR
440 (BP10)
375 RW
445
P14CB
p16 CB3
RA
EXP


I need to extract only the data with the format in the bold rows (numbers - in the beginning and text after). The rows with only numbers or only text should be removed. 

Any suggestions for a DAX formula, or a formula in the Advanced query? (the table is in Import mode)

Thank you.

Need Help
Show and Tell
  • themistoklis's avatar
    themistoklis
    5 years ago

    Anonymous 

     

    Try the following formula. Then add it to a visual filter.

     

     

    Filter_Values = IF(NOT ISERROR(VALUE((LEFT(Sheet1[Subject],1)))) && ISERROR(VALUE(RIGHT(Sheet1[Subject],1))),1,0)

     

     

2 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    5 years ago

    Anonymous 

     

    Try the following formula. Then add it to a visual filter.

     

     

    Filter_Values = IF(NOT ISERROR(VALUE((LEFT(Sheet1[Subject],1)))) && ISERROR(VALUE(RIGHT(Sheet1[Subject],1))),1,0)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi themistoklis ,

      Thank you so much. It works!

      Best,
      Ema