get value from cell in a range

[FROGGER24]

I have several hundred workbooks that the Identification number is in various cells between H2 and P4. In cell AI1 would like a formula that will get the Identification Number no matter what cell it is in between H2 and P4

Thanks in advance

 

[Mentor82]

Hi,
I recommend you upload your file with test data on dropbox in order to have a better overview of what you have and check how to help you. What do you reckon?

 

[Peter_SSs]

Peter, when I copied and pasted into cell outside of range I got 1.

Hmm, that seems strange. Could you either do what Mento82 has suggested or when you get the #REF ! error (with no merged cells), tell me ..

- what cell the only text is in
- what that text is
- what these two formulas return when placed in cells outside the H2:P4 range.
=SUMPRODUCT((H2:P4<>"")*ROW(H2:P4))-ROW(2:2)+1
=SUMPRODUCT((H2:P4<>"")*COLUMN(H2:P4))-COLUMN(H:H)+1

 

[FROGGER24]

Formula 1 returned 4, formula 2 returned 17. The text is BARGE "KIRBY 28180" currently in L2, but on different worksheets it could be in M2. The worksheets are different since it is from multiple vendors. When I look for blank cells using the f5 function and special selecting blanks it indicates that a cell L3 could contain a space since it was not shaded

 

[FROGGER24]

Mentor, I tried your function again and get error message #Value !. I tried to narrow the range down to just two cells L2:L3 and got the same error

 

[Mentor82]

Hi,
I just found out I made a mistake in the own developed function I provided you with. Here's the function fixed. Pls check it now

Function GetId(IdRange as range) as string
   Dim rCell as range
   For each rCell in IdRange
       If isnumeric(rCell.value)=false then
            GetId = cstr(rCell.value)
            Exit for
       End if
   Next

End function

 

[Mentor82]

Hi,
You can also try this own-developed function which is the previous one extended by verification if.for.some reason value in the provided range is not a date format too.

Function GetId(IdRange as range) as string
   Dim rCell as range
   For each rCell in IdRange
       If IsNumeric(rCell.value)=false and IsDate(rCell.value)=false then
            GetId = cstr(rCell.value)
            Exit for
       End if
   Next

End function

 

[FROGGER24]

Mentor the last one worked, is there way to hard code the range into function? I will using this in a workbook loop on about 13k workbooks

 

[Peter_SSs]

Mentor the last one worked, is there way to hard code the range into function? I will using this in a workbook loop on about 13k workbooks

Firstly, given the above, would you still be interested in a standard formula solution?

If so, let's consider this

Formula 1 returned 4, formula 2 returned 17. The text is BARGE "KIRBY 28180" currently in L2, but on different worksheets it could be in M2. The worksheets are different since it is from multiple vendors. When I look for blank cells using the f5 function and special selecting blanks it indicates that a cell L3 could contain a space since it was not shaded

If L3 does in fact contain a space (or spaces) then that would mean that "KIRBY 28180" is not the only text in the range and would therefore account for the #REF error because my formula was only expecting to find one thing in the range. So when it found 2 things, the first extra formula that I gave you in post 12 returned 4 and so my main formula was looking for the required value in row 4 of the H2:P4 range. Clearly that is not possible since that range only has 3 rows, hence the error. Similar with the other formula returning 17 when the H2:P4 range does not contain 17 columns.

However, if there is a space in L3 then the =COUNTIF(H2:P4,"?*") formula that I gave you in post 9 would not return 1 as you said in post 10. Perhaps you had a different data set then?

If you still have that data with "something" that is not visible in L3, what do these formulas return?
=CODE(L3)
=LEN(L3)


Would you "identification numbers" have a minimum length? That is, can we say that every identification number is at least 4 characters long? Or some other number?
If so, and any other stray cells containing spaces or whatever are less than 4 characters, then you could try this version

=INDEX(H2:P4,SUMPRODUCT((LEN(H2:P4)>3)*ROW(H2:P4))-ROW(2:2)+1,SUMPRODUCT((LEN(H2:P4)>3)*COLUMN(H2:P4))-COLUMN(H:H)+1)


Also, if you still have that data set with the "something" in L3, what happens if you delete "KIRBY 28180" from L2 and type "KIRBY 28180" into cell L4 and run Mentor's function on that data?

Finally, note that your idea of merging the whole range will fail if the identification number is in a row below or on the same row to the right of a cell like your L3.

 

[FROGGER24]

Peter, yes formula would be great, =CODE(L3) returned 34,=LEN(L3) returned 1. The identification numbers range from 3 to 35(raw data)
"Also, if you still have that data set with the "something" in L3, what happens if you delete "KIRBY 28180" from L2 and type "KIRBY 28180" into cell L4 and run Mentor's function on that data?" - no value returned

 

[Peter_SSs]

=CODE(L3) returned 34,=LEN(L3) returned 1.

That tells us that the is a double quote mark (") in L3. You should be able to see that unless there is formatting to make that mark the same colour as the background, or vice-versa. In any case, we have established that your identification number is (or may) not be the only text in the H2:P4 range.

"Also, if you still have that data set with the "something" in L3, what happens if you delete "KIRBY 28180" from L2 and type "KIRBY 28180" into cell L4 and run Mentor's function on that data?" - no value returned

Assuming you have corrected r.Cell to rCell in two places in mentor's function, it should return the double quote mark. In any case it may not do what you want if there is more than one cell with text.

Peter, yes formula would be great, ...The identification numbers range from 3 to 35(raw data)

OK, so the formula I suggested in post 18, with this slight modification should work for you - provided that your identification number is the only text 3 characters or more in the range.

=INDEX(H2:P4,SUMPRODUCT((LEN(H2:P4)>2)*ROW(H2:P4))-ROW(2:2)+1,SUMPRODUCT((LEN(H2:P4)>2)*COLUMN(H2:P4))-COLUMN(H:H)+1)