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,
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?

 

[FROGGER24]

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

 

[Mentor82]

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.

 

[FROGGER24]

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

 

[Mentor82]

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.

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

 

[Peter_SSs]

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)

 

[FROGGER24]

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.

Application.DisplayAlerts = False
Range("H2:P4").Merge
Range("AI1").Value = Range("H2").Value 'name
Range("H2:P4").UnMerge
Application.DisplayAlerts = True

 

[Peter_SSs]

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.

... 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,"?*")

 

[FROGGER24]

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