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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Last edited by a moderator:
Upvote 0
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,"?*")
 
Upvote 0
Peter, when I copied and pasted into cell outside of range I got 1.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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