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

 

[Peter_SSs]

Try this in a copy of your workbook.

If you have large data and this takes too long, post back for a modification.

Sub Find_Pattern()
  Dim c As Range
  Dim itm As Variant
  Dim i As Long
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[A-Z]\d{7}(?=\D|$)"
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
      i = 0
      For Each itm In .Execute(c.Value)
        i = i + 1
        c.Offset(, i).Value = itm
      Next itm
    Next c
  End With
End Sub

My sample data (col A) & code results (cols B:D)

Book1
	A	B	C	D
1	faerfarefr H0000001 eargargagagaH0000004fggf	H0000001	H0000004
2
3	H0000002	H0000002
4	abc H00000078
5	dfagagsf H0000003 fdsghdgfhsfgh	H0000003
6	324radsfcdf H0000004 dfrtsrwstg	H0000004
7	rwrw
8	gfffsgbbbbbbbbbbbvcH0000005 fgdgfgfgfgdsdr5646hgfgxhgf	H0000005
9	H0000006 fdsgdgf H0000010 vcbxbv H0000012	H0000006	H0000010	H0000012
10	gfffsgbbbbbbbbbbbvc H0000005fgdgfgfgfgdsdr5646hgfgxhgf	H0000005
11	dfgzdgfg H0000008fdgsdgfg H0000001	H0000008	H0000001
12	H8767654 B8787654	H8767654	B8787654
Sheet1

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub MoveANNNNNNNtoNextColumn()
  Dim R As Long, C As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To [B][COLOR="#FF0000"]10[/COLOR][/B])
  For R = 1 To UBound(Data)
    C = 0
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X) Like "[A-Z]#######*" Then
        C = C + 1
        Result(R, C) = Mid(Data(R, 1), X, 8)
      End If
    Next
  Next
  Range("B1").Resize(UBound(Result), [B][COLOR="#FF0000"]10[/COLOR][/B]) = Result
End Sub[/td]
[/tr]
[/table]

Note that I have assumed there will be no more than 10 individual codes contained within any single cell in Column B. If there could be more, than increase the red highlighted numbers to a value larger than the most codes any one cell could contain.

 

[Peter_SSs]

@gint32
I am not sure what your users might type into the cells or what exact consequences you want but note that my code & Rick's do a slightly different job.
For example, refer to the text in cell A4 of my previous post.

- My code extracts nothing from that text because it was not a letter followed by (exactly) 7 digits. This could mean that you are missing a value for your lookup but to me it is likely a data entry error and I wouldn't be sure which digit was the accidental extra digit.

- Rick's code extracts the letter and the following 7 digits, omitting the final digit (8). To me, this could mean that your lookup is looking up the wrong value so may inadvertently return an incorrect result.

That is both codes may possibly result in an imperfect result, so I'm just giving you a couple of things to consider when choosing, or perhaps providing more information so that we can refine our codes if required.

 

[Peter_SSs]

Further to my last post,

- if you wanted to report any items that were a (capital) letter followed by at least 7 digits but mark those with more than 7 digits then there would be just 2 changes to the code I previously posted

<del>.Pattern = "[A-Z]\d{7}(?=\D|$)"</del>
.Pattern = "[A-Z]\d{7,}"

<del>C.Offset(, i).Value = itm</del>
C.Offset(, i).Value = itm & IIf(Len(itm) = 8, "", " (" & Len(itm) - 1 & ")")

Sample output

Book1
	A	B	C	D
3	H0000002	H0000002
4	abc H00000078	H00000078 (8)
5	dfagagsf H0000003 fdsghdgfhsfgh	H0000003
6	A324radsfcdf H0000004 dfrtsrwstg	H0000004
7	rwrw
8	gfffsgbbbbbbbbbbbvcH0000005 fgdgfgfgfgdsdr5646hgfgxhgf	H0000005
9	H0000006 fdsgdgf H0000010 vcbxbv H0000012568	H0000006	H0000010	H0000012568 (10)
>7

- if you wanted to report any items that were a (capital) letter followed by any number of digits but mark those with other than 7 digits ..

<del>.Pattern = "[A-Z]\d{7}(?=\D|$)"</del>
.Pattern = "[A-Z]\d+"

<del>C.Offset(, i).Value = itm</del>
C.Offset(, i).Value = itm & IIf(Len(itm) = 8, "", " (" & Len(itm) - 1 & ")")

Sample output

Book1
	A	B	C	D
3	H0000002	H0000002
4	abc H00000078	H00000078 (8)
5	dfagagsf H0000003 fdsghdgfhsfgh	H0000003
6	A324radsfcdf H0000004 dfrtsrwstg	A324 (3)	H0000004
7	rwrw
8	gfffsgbbbbbbbbbbbvcH0000005 fgdgfgfgfgdsdr5646hgfgxhgf	H0000005
9	H0000006 fdsgdgf H0000010 vcbxbv H0000012568	H0000006	H0000010	H0000012568 (10)
Not 7

 

[gint32]

Thanks very much everyone..though OMG.I think i bitten off more than i can chew..as its all of a sudden getting pretty complicated ..as in the next steps i thought i could handle on my own ..phew!

 

[gint32]

Never thought about those...dp thanks for pointing it out

 

[Peter_SSs]

Thanks very much everyone..though OMG.I think i bitten off more than i can chew..as its all of a sudden getting pretty complicated ..

Most likely much of the complication is because we don't know exactly what is, or might be, in your sheet so we are trying to make allowance for different circumstance that may never happen in your sheet.

In any case, I hope you can get something useful out of what has been offered here. And post back with further details if you need more help on this.

 

[gint32]

Most likely much of the complication is because we don't know exactly what is, or might be, in your sheet so we are trying to make allowance for different circumstance that may never happen in your sheet.

In any case, I hope you can get something useful out of what has been offered here. And post back with further details if you need more help on this.

Of course and thanks. I am attempting to explain as I type, I'll be posting back shortly.

 

[gint32]

Most likely much of the complication is because we don't know exactly what is, or might be, in your sheet so we are trying to make allowance for different circumstance that may never happen in your sheet.

In any case, I hope you can get something useful out of what has been offered here. And post back with further details if you need more help on this.

Thanks for that Pete,
Yes, I do feel l need to give the whole picture here, just in case I am headed in the wrong direction with this problem and the fact that there might be an easier way to achieve my end outcome.
I have a two workbooks:
One workbook1.sheet1 (Col-A )holds all the ordered column …A_Z customer ID’s (A0000001, A0000002, etc) with a column (F) that c ontains free text with its unordered ID’s within.
The second Workbook2.sheet1 (<1000 records) holds a small amount of Customer id’s
What I am hoping to achieve is match any corresponding records in the free text (Col-F) within Workbook1.sheet1 (to the the ones that your kindly supplied Vba loops through and picks out for me with Workbook2.sheet1 (<1000 records), trouble is I don’t knowhow to go about matching more than one as your code works fine and does pull them into their respective adjacent columns, as their can be as much of 15-20 records extracted from each of the free text records. So I am not sure if I should be separating these into a separate Columns or combining all of the hits found into just the one column and then performing some sort of vlookup.

 

[gint32]

Most likely much of the complication is because we don't know exactly what is, or might be, in your sheet so we are trying to make allowance for different circumstance that may never happen in your sheet.

In any case, I hope you can get something useful out of what has been offered here. And post back with further details if you need more help on this.

Hi, Does anyone have any ideas on this?