I have a data table that looks like this:
And am using the following forumula:
=MID(LEFT(F2,FIND("D",F2)-1),FIND(" ",F2)+1,LEN(F2))
With the goal of extracting the numeric value after the "D" character in my Visit column. Some visits have 1 digit after the "D", some are 2, max will be 3 digits. However, the formula I’ve tried to use above is returning blanks and #VALUE! only. I figured the number character between the D and the space would work, but then I realize not all will have the space (see C2D16). Can someone explain what I am doing incorrectly?
TIA
>Solution :
EDIT
Here is an explanation of how the below formula works,
=MAX(IFERROR(MID(SUBSTITUTE($F2,"PREDOSE",""),ROW($1:$10),3)/1,""))
First we are removing or substituting the word PREDOSE from the string, even if we don’t have the word the function shall ignore and leave it as it is
=SUBSTITUTE($F2," PREDOSE","")
Next we are wrapping the formula with an MID function and for the start number we are using ROW function which breaks the string into 10 segments
=MID(SUBSTITUTE($F2," PREDOSE",""),ROW($1:$10),3)
On selecting the formula & press F9 or if you goto Formulas tab and evaluate you shall see it gives us an array
{"C2D";"2D1";"D1";"1";"";"";"";"";"";""}
Therefore we need to ignore the text part from the array, so we can either multiply by 1, divide by 1, add 0 or we can use double minus(--) which negates the text values as #VALUE! error while leaves the numeric part
So, to exclude the error values we just wrap it within an IFERROR Function
=IFERROR(--MID(SUBSTITUTE($F2," PREDOSE",""),ROW($1:$10),3),"")
Which again on selecting and pressing F9 shall give us an array of number and blanks
{"";"";"";1;"";"";"";"";"";""}
Last but not least, we need the numeric as an output, hence MAX comes to save us
=MAX(IFERROR(--MID(SUBSTITUTE($F2," PREDOSE",""),ROW($1:$10),3),""))
and gives an output as we desire!
