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

CodePudding user response：

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!

CodePudding user response：

Your original approach would work as well, as long you handle the error. In order to investigate errors, like #value it's always a good idea to break down the formula into pieces to locate the issue.

=MID(F2,FIND("D",F2) 1,IFERROR(FIND(" ",F2)-FIND("D",F2)-1,LEN(F2)-FIND("D",F2)))

CodePudding user response：

Another:

=-LOOKUP(0,-(MID(F2,FIND("D",F2) 1,{1;2;3})&"**0"))

CodePudding user response：

Here is a formula that I think is a little cleaner

=MID(F1,FIND("D",F1) 1,IFERROR(FIND(" ",F1),LEN(F1) 1)-(FIND("D",F1) 1))

I think this actually represents the thought process of what you are trying to do.

Breaking it down:

=MID(F1,X,Y)

You want the middle part of F1, starting at X with a Length of Y

X=FIND("D",F1) 1

Starting point (X) is one space after the first D

Y=IFERROR(FIND(" ",F1),LEN(F1) 1)-(FIND("D",F1) 1)

Length (Y) is X subtracted from either the position of the space character or 1 more than the length of the entire string