Need to have blank cell if there is specific value in my cell

Question

Hi team,&nbsp;&nbsp;Lets say I have a dataset with the following values. I want to automatically empty the cell in cell 2 or replace with 0 while the remaining cells will show 0.5 &amp; 9.5 accordingly. I'm open to structuring this in excel. For now, I'm using power query to covert all this into text and have 2-4 steps of replace values then getting the data.&nbsp;&nbsp;Looking for any other efficient way if available within Power query, but just Excel is also fine. Tq&nbsp;( Pending - 0.5)( Rejected - 01)( Approved -9.5)

oliverscheurich · Answer

bsrujan1210&nbsp;=TRIM(IF(ISNUMBER(SEARCH("Rejected",A1)),"",MID(A1,FIND("-",A1)+1,LEN(A1)-1-FIND("-",A1))))Does this return the intended result? The cells which contain "Rejected" are cleared and the values from the other cells are extracted.

sergeibaklan · Answer

bsrujan1210&nbsp;
What's wrong with Power Query? The step could be
    addValue = Table.AddColumn(
        Source,
        "Value",
        each [
            n = Number.From( Text.Trim( Text.BetweenDelimiters([Data], "-", ")" )) ),
            b = if List.Contains({0.5, 9.5}, n)
                then n
                else null][b]
    )

Forum Discussion

Need to have blank cell if there is specific value in my cell

2 Replies

Resources