Find and display variable text within multiple cells

[damiencarl]

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>

 

[tonyyy]

damiencarl,

Welcome to the Board.

You might consider the following...

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]

1. damiencarl,

Welcome to the Board.

You might consider the following...

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]

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

 

[FormR]

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 />

 

[tonyyy]

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

[h=3]Copy Excel VBA Code to a Regular Module[/h]