Formula for extracting out text entries

[jc132568]

I am using the following to pull out data based on the presence of text in a
particular column:
In B2
=IF(ISERROR(SMALL($F:$F,ROW(B1))),"
",INDEX(PC3005!B:B,MATCH(SMALL($F:$F,ROW(B1)),$F:$F,0)))
In F2
and =IF(TRIM(PC3005!Y2)<>"",ROW()," ")

It works great and is part of our everyday sheets. I am adapting this for
another worksheet application where I want to achieve this same function but
further down the worksheet, say in B152 and F152. The formula doesn't work!
Do I have to have the formula at the top of the worksheet for it to work.
What have I missed?

=IF(ISERROR(SMALL($F:$F,ROW(B152))),"
",INDEX(AntibacterialDrugs!A:A,MATCH(SMALL($F:$F,ROW(B152)),$F:$F,0)))

=IF(TRIM(AntibacterialDrugs!O2)<>"",ROW()," ")

Thanks
jc

 

[Jacob Skaria]

ROW(B1) within the SMALL function denotes the 1st smallest, 2nd smallest etc;
and so that should be ROW(B1) instead of ROW(B152) which denotes 152nd
smallest

If this post helps click Yes

 

[ryguy7272]

You can use this function to extract text from a cell:
Function RemDigits(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

Call it like this:
=RemDigits(A1)


HTH,
Ryan---

 

[T. Valko]

Use the ROWS function instead of the ROW function and use the cell reference
of the cell in which the formula is being entered.

For example, if the formula is being entered in cell B152 and then copied
down, use:

ROWS(B$152:B152)