# Thread: get value from cell in a range Thanks: 0 Likes: 0

1. ## get value from cell in a range

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

2. ## Re: get value from cell in a range

Hi,
How am I supposed to identify whether the vakue in the cell is an ID number or not? Is your ID some kind of sequence number? What is the pattern of it?

3. ## Re: get value from cell in a range

Hi Mentor, it will be text string (alpha numeric))

4. ## Re: get value from cell in a range

Hi,
Thanks for your reply but it does not tell me much though. Does it have a constant lenght? If so, how long is it? How many letters and cell are within the Id? Are all of your ids with the same pattern? Can you write down a example od your id? I'm asking these questions because I'm trying to finger out how I'm meant to distinguish between id an any other text/value you might have among those cells.

5. ## Re: get value from cell in a range

that will be the only text in that range the text length will vary since it will be different barge companies with different name conventions such as Barge Kirby 10000, Barge Kirby 10000B,CTCO 130 or even just number such as 303

6. ## Re: get value from cell in a range

Hi,
Check iut this own-developed function GetId. Copy it.to the module of the workbook you have and try to use it, the GetId function and let me knkw if that's ok for you. The function requires one range paramater IdRange where you should provide a range of cells (H2:P4) of which one includes id you want to get.
Code:
```Function GetId(IdRange as range) as string
Dim rCell as range
For each rCell in IdRange
If isnumeric(r.Cell.value)=false then
GetId = cstr(r.Cell.value)
Exit for
End if
Next

End function```

7. ## Re: get value from cell in a range

Originally Posted by trimmer69
that will be the only text in that range ...
In that case, if you have a recent version of Excel with the CONCAT function, use this in AI1

=CONCAT(H2:P4)

If you don't have the CONCAT function then try

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

8. ## Re: get value from cell in a range

thanks for function, but I get error message #Value !. I ended up using the merge/unmerge for that range which moved the text into H2. Thanks for the help

Peter_SS the concat formula gave error #Name ! and the last one gave error #Ref !

I ended up merging the range.

Range("H2:P4").Merge
Range("AI1").Value = Range("H2").Value 'name
Range("H2:P4").UnMerge

9. ## Re: get value from cell in a range

Originally Posted by trimmer69
Peter_SS the concat formula gave error #Name !
I mentioned that you needed a recent Excel version to use CONCAT & that error message indicates that you version does not have that function.

Originally Posted by trimmer69
... and the last one gave error #Ref !
That would indicate to me that your statement "that will be the only text in that range" is not strictly true. If it appears there is no other text in the range then perhaps you have one or more cells containing blank spaces or formula that returns " " or similar, making them appear blank to the user, but not to the formula. Out of interest, if you still have a sheet that is giving that REF error with H2:P4 unmerged, what does this formula, placed in a vacant cell, return?
=COUNTIF(H2:P4,"?*")

10. ## Re: get value from cell in a range

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