get value from cell in a range

FROGGER24

Well-known Member
Joined
May 22, 2004
Messages
704
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
 
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?
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
Code:
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
 
Upvote 0
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.
Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
=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)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top