Forum Discussion
retrieve text/string from cells in excel
Hi everyone,
Thanks for your attention. I would like to retrieve several information to made counts of a certain information as for ETL. My question is that how can I write the DAX script to extract text from a certain cell in excel? The following picture is the example, how do I retrieve the red words?
Really appreciate for your support. Thank you. :-)
You may add a calculated column as follows.
Column = VAR s1 = "3. Defect type:" VAR s2 = "4. Initiator:" VAR p1 = SEARCH ( s1, Table1[Abnormal Description] ) VAR p2 = SEARCH ( s2, Table1[Abnormal Description], p1 ) RETURN MID ( Table1[Abnormal Description], p1 + LEN ( s1 ), p2 - p1 - LEN ( s1 ) )
6 Replies
Hi AlexLiang
This is a rough approach but you could introduce calculated columns to detect it using DAX
something like
Column contains Crack =
IF( FIND( "Crack", 'Table1'[Column3], 1, blank() ) > 0 , TRUE(), FALSE())Hi Phil_Seamark
Thanks for your feedback.
What if I want to retrieve all kinds of "defect type" and put them in a new column?
Because I may need to calculate the frequency of each defect type.
Thank you again. :-)
Do you have a predefined list of defects? Do you need to maintain a separate count for each type?
You may add a calculated column as follows.
Column = VAR s1 = "3. Defect type:" VAR s2 = "4. Initiator:" VAR p1 = SEARCH ( s1, Table1[Abnormal Description] ) VAR p2 = SEARCH ( s2, Table1[Abnormal Description], p1 ) RETURN MID ( Table1[Abnormal Description], p1 + LEN ( s1 ), p2 - p1 - LEN ( s1 ) )
