Find and display variable text within multiple cells

damiencarl

New Member
Joined
Dec 4, 2017
Messages
3
I have a set of names that all begin with the same 4 characters, but the remaining characters are variable (e.g. ABC1111, ABC2222, ABC3333...). Also, the names show up in variable columns within the thousands of lines of data (columns A-G). Is there a way to search the lines of data for variable string "ABC*" and return the entire name in a single column (G)?

A
B
C
D
E
F
G
H
data
data
data
data
data
data
ABC1111
ABC1111
data
data
data
data
data
ABC2222
data
ABC2222
data
data
data
data
data
data
data
ABC3333
ABC4444
data
data
data
data
data
data
ABC4444

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
damiencarl,

Welcome to the Board.

You might consider the following...

Code:
Sub GetNames_1033973()
Dim str As String
Dim rng As Range
Dim r As Long, c As Long
Dim arr As Variant

str = "ABC" 'Change to match your search string
Set rng = Range("A1:H" & Cells(Rows.Count, "A").End(xlUp).Row)
arr = rng.Value

For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    For c = 1 To 7
        If InStr(arr(r, c), str) = 1 Then
            arr(r, 8) = arr(r, c)
            Exit For
        End If
    Next c
Next r

Range("A1:H" & Cells(Rows.Count, "A").End(xlUp).Row).Value = arr
End Sub

Full names will be placed in Column H, not Column G.

Cheers,

tonyyy
 

damiencarl

New Member
Joined
Dec 4, 2017
Messages
3
  1. damiencarl,
Welcome to the Board.

You might consider the following...

Code:
Sub GetNames_1033973()
Dim str As String
Dim rng As Range
Dim r As Long, c As Long
Dim arr As Variant

str = "ABC" 'Change to match your search string
Set rng = Range("A1:H" & Cells(Rows.Count, "A").End(xlUp).Row)
arr = rng.Value

For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    For c = 1 To 7
        If InStr(arr(r, c), str) = 1 Then
            arr(r, 8) = arr(r, c)
            Exit For
        End If
    Next c
Next r

Range("A1:H" & Cells(Rows.Count, "A").End(xlUp).Row).Value = arr
End Sub

Full names will be placed in Column H, not Column G.

Cheers,

tonyyy

Thanks Tony, but where would I place this code within the spreadsheet to return the values in column H?
 

damiencarl

New Member
Joined
Dec 4, 2017
Messages
3
Thanks Tony, but where would I place this code within the spreadsheet to return the values in column H?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Is there a way to search the lines of data for variable string "ABC*" and return the entire name in a single column (G)?

Hi, here is a formula option you can try - note: note sure where ABC3333 came from for your third row of example data.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A</td><td style=";">B</td><td style=";">C</td><td style=";">D</td><td style=";">E</td><td style=";">F</td><td style=";">G</td><td style=";">H</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">ABC1111</td><td style="background-color: #FFFF00;;">ABC1111</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">ABC2222</td><td style=";">data</td><td style=";">ABC2222</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">ABC4444</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">data</td><td style=";">ABC4444</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=INDEX(<font color="Blue">A2:G2,MATCH(<font color="Red">"ABC*",A2:G2,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,958
Members
414,352
Latest member
macquarie_jchan58

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