Only Return Values that Start with a Specific Letter

silverbluemoon

New Member
Joined
May 19, 2010
Messages
20
I found the following awesome script on digital-help.com that allows me to lookup and return multiple values concatenated into one cell (which I need to do):

Code:
[COLOR=#000000]Function Lookup_concat(Search_string As String, _[/COLOR]
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
   If Search_in_col.Cells(i, 1) = Search_string Then
   result = result & " " & Return_val_col.Cells(i, 1).Value
   End If
NextLookup_concat = Trim(result)
[COLOR=#000000]End Function[/COLOR]

THEN, I only have to enter THIS in each cell:

Code:
=Lookup_concat(Look_up_value, Search_in_column, Concatenate_values_in_column)

This PERFECT, except that I only need to return values that start with a specific letter. I need to figure this out quickly, so please help if you can! Thank you!!
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I found the following awesome script on digital-help.com that allows me to lookup and return multiple values concatenated into one cell (which I need to do):

Code:
[COLOR=#000000]Function Lookup_concat(Search_string As String, _[/COLOR]
  Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To Search_in_col.Count
   If Search_in_col.Cells(i, 1) = Search_string Then
   result = result & " " & Return_val_col.Cells(i, 1).Value
   End If
NextLookup_concat = Trim(result)
[COLOR=#000000]End Function[/COLOR]

THEN, I only have to enter THIS in each cell:

Code:
=Lookup_concat(Look_up_value, Search_in_column, Concatenate_values_in_column)

This PERFECT, except that I only need to return values that start with a specific letter. I need to figure this out quickly, so please help if you can! Thank you!!

I have modified the code for you... to use it, pass the letter you want the found cells to start with followed by an asterisk (both characters in quote marks) into the first argument.
Code:
Function Lookup_concat(Search_string As String, _
   Search_in_col As Range, Return_val_col As Range)
  Dim i As Long
  Dim result As String
  For i = 1 To Search_in_col.Count
    If Search_in_col.Cells(i, 1) Like Search_string Then
      result = result & " " & Return_val_col.Cells(i, 1).Value
    End If
  Next
  Lookup_concat = Trim(result)
End Function
 

silverbluemoon

New Member
Joined
May 19, 2010
Messages
20
I have modified the code for you... to use it, pass the letter you want the found cells to start with followed by an asterisk (both characters in quote marks) into the first argument.
Code:
Function Lookup_concat(Search_string As String, _
   Search_in_col As Range, Return_val_col As Range)
  Dim i As Long
  Dim result As String
  For i = 1 To Search_in_col.Count
    If Search_in_col.Cells(i, 1) Like Search_string Then
      result = result & " " & Return_val_col.Cells(i, 1).Value
    End If
  Next
  Lookup_concat = Trim(result)
End Function

Thank you so much, but I don't see the change in the code above. Am I just missing it?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Thank you so much, but I don't see the change in the code above. Am I just missing it?
I changed the equal sign to the word "Like" (Like is a comparison operator that let's you use wildcards in a text pattern for the comparison).
 

silverbluemoon

New Member
Joined
May 19, 2010
Messages
20

ADVERTISEMENT

Oh, I see! So, please forgive yet another beginner question, but where do I put the letter I want it to find? And this will give me the string I want it to find at the beginning of the contents of the cell?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Oh, I see! So, please forgive yet another beginner question, but where do I put the letter I want it to find? And this will give me the string I want it to find at the beginning of the contents of the cell?

Sorry, since you posted the original function, I figured you were familiar with them. First of all, since it is a function, actually more technically a UDF (User Defined Function), you use it just like any other Excel function... if you want the output to appear in, say, cell C2, you put the formula (basically for your intended use, an equal sign followed by the function) in cell C2. The function takes three arguments... the first argument is a text value consisting of the letter you want to find at the beginning of the text followed by an asterisk (which is a wildcard standing in for zero or more other characters), the second argument is the range you want to search for that begins with that letter and the third argument is the range you want to lookup and return values from. So, let's say you wanted to search A2:A100 for text starting with the letter "E" and return the corresponding values in C2:C10, the formula you would in the cell you want to display the concatenated values would be this...

=Lookup_concat("E*",A2:A100,C2:C100)

Alternately, if you wanted store the letter in a cell, say C1, then your formula would look like this...

=Lookup_concat(C1&"*",A2:A100,C2:C100)
 

silverbluemoon

New Member
Joined
May 19, 2010
Messages
20
Oh I see. I am familiar, in general, how arguments are passed to the function -- in this case the Lookup_concat takes a cell and looks up it up in another column, then returns the value from yet another column. But in this case, the search list is a bunch of part numbers. I have thousands and cannot change them all to add an asterisk; the part numbers must remain the same. I am looking up a list of part numbers I have in another list and getting all t he values that apply. In this case, all of our vehicles that these parts go into. So is there a way to tell the function to search for a specific value I specify, without it being in the search (part numbers I'm searching for) column?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Oh I see. I am familiar, in general, how arguments are passed to the function -- in this case the Lookup_concat takes a cell and looks up it up in another column, then returns the value from yet another column. But in this case, the search list is a bunch of part numbers. I have thousands and cannot change them all to add an asterisk; the part numbers must remain the same. I am looking up a list of part numbers I have in another list and getting all t he values that apply. In this case, all of our vehicles that these parts go into. So is there a way to tell the function to search for a specific value I specify, without it being in the search (part numbers I'm searching for) column?
Okay, let's see if I have this correct... you have a column of part numbers and you want to look something up based on the leftmost character of that part number and concatenate them together. If that is correct, then you are going to have to tell us what column the part numbers are listed in, what column their first character should be looked up in and what column the concatenations should come from.
 

Forum statistics

Threads
1,144,376
Messages
5,723,998
Members
422,530
Latest member
Badpoisondwarf

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
Top