vba to find specific combination of chars in one column

[gint32]

Hi all
I have a column that contains free text, I need a way (using vba) to find specific chars in this column the format of the text chars will always be like .. one Alpha and 7 numeric(together).....Examples of this is are D1564567, A1235567, all the way through to Z2356457 and if and when found copy this alpha numeric to he adjacent cell on the right. Not all cells will have this so it needs to e able to skip over records that do not contain, any help appreciated

 

[Rick Rothstein]

You did not say what column had your values, so I assumed Column A with the output going to Column B.

Sub MoveANNNNNNNtoNextColumn()
  Dim R As Long, Data As Variant
  Data = Range("[B][COLOR="#FF0000"]A[/COLOR][/B]1", Cells(Rows.Count, "[B][COLOR="#FF0000"]A[/COLOR][/B]").End(xlUp))
  For R = 1 To UBound(Data)
    If Not Data(R, 1) Like "[A-Z]#######" Then Data(R, 1) = ""
  Next
  Range("[B][COLOR="#FF0000"]B[/COLOR][/B]1").Resize(UBound(Data)) = Data
End Sub

 

[Peter_SSs]

.. another way.

Sub CheckPattern()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(AND(ABS(CODE(LEFT(RC[-1]&"" "",1))-77.5)<13,MAX(ABS(CODE(MID(RC[-1]&REPT("" "",8),{2,3,4,5,6,7,8},1))-52.5))<5,LEN(RC[-1])=8),RC[-1],"""")"
    .Value = .Value
  End With
End Sub

 

[Rick Rothstein]

And here is yet another way to write the code without using a loop...

[table="width: 500"]
[tr]
	[td]Sub CheckPattern()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("IFERROR(IF((ABS(CODE(LEFT(@))-77.5)<13)*(ABS(RIGHT(@,7)-5000000)<5000001)*(LEN(@)=8),@,""""),"""")", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

And here is yet another way to write the code without using a loop...

But not robust - because of the 'usual suspects'. For example, A12DEC69

 

[gint32]

And here is yet another way to write the code without using a loop...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CheckPattern()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("IFERROR(IF((ABS(CODE(LEFT(@))-77.5)<13)*(ABS(RIGHT(@,7)-5000000)<5000001)*(LEN(@)=8),@,""""),"""")", "@", .Address))
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

thanks, I have ran the suggested script on the following test data and can't get it to work. I find it'll only pull if the cell contents have just these chars combination only.

the only data it get is in line 2 = H0000002 all the rest are blank

[TABLE="width: 364"]
<colgroup><col></colgroup><tbody>[TR]
[TD]faerfarefr H0000001 eargargagagaH0000004fggf
[/TD]
[/TR]
[TR]
[TD]H0000002
[/TD]
[/TR]
[TR]
[TD]dfagagsf H0000003 fdsghdgfhsfgh
[/TD]
[/TR]
[TR]
[TD]324radsfcdf H0000004 dfrtsrwstg[/TD]
[/TR]
[TR]
[TD]gfffsgbbbbbbbbbbbvcH0000005 fgdgfgfgfgdsdr5646hgfgxhgf
[/TD]
[/TR]
[TR]
[TD]H0000006 fdsgdgf H0000010 vcbxbv H0000012
[/TD]
[/TR]
[TR]
[TD]gfffsgbbbbbbbbbbbvc H0000005fgdgfgfgfgdsdr5646hgfgxhgf
[/TD]
[/TR]
[TR]
[TD]dfgzdgfg H0000008fdgsdgfg H0000001
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gint32]

I have tried your suggestion and get the same..I am thinking that you may not of got what i meant by free text, so heres another go.
What i mean the input user types free text (speech) in each cell within the column A but at some point within the free text and its really up to them (craZy I know, so dont ask) they may put in this reference(A#######) into the cell( but not always), so if they did then i just want to find the cells that have this and drag it out to the next column...and therfore the ones that don't contain this B####### or Z####### will be discarded..as its the reference number that i need for a vlookup

 

[Rick Rothstein]

thanks, I have ran the suggested script on the following test data and can't get it to work. I find it'll only pull if the cell contents have just these chars combination only.

the only data it get is in line 2 = H0000002 all the rest are blank

[TABLE="width: 364"]
<colgroup><col></colgroup><tbody>[TR]
[TD]faerfarefr H0000001 eargargagagaH0000004fggf
[/TD]
[/TR]
[TR]
[TD]H0000002
[/TD]
[/TR]
[TR]
[TD]dfagagsf H0000003 fdsghdgfhsfgh
[/TD]
[/TR]
[TR]
[TD]324radsfcdf H0000004 dfrtsrwstg[/TD]
[/TR]
[TR]
[TD]gfffsgbbbbbbbbbbbvcH0000005 fgdgfgfgfgdsdr5646hgfgxhgf
[/TD]
[/TR]
[TR]
[TD]H0000006 fdsgdgf H0000010 vcbxbv H0000012
[/TD]
[/TR]
[TR]
[TD]gfffsgbbbbbbbbbbbvc H0000005fgdgfgfgfgdsdr5646hgfgxhgf
[/TD]
[/TR]
[TR]
[TD]dfgzdgfg H0000008fdgsdgfg H0000001
[/TD]
[/TR]
</tbody>[/TABLE]

What should be done for the cells containing more than one reference number (see red highlighted text above)?

 

[gint32]

Thanks for staying with me on this. Like i said I'll be using a simple vlookup on the ones that are found so might be best to have each one separated and put in a new adjacent cell. If that's at all possible. Thanks again

 

[gint32]

What should be done for the cells containing more than one reference number (see red highlighted text above)?

As in a separate cell for each found..thanks