VBA: Copy a string pattern with wildcards from an array to a new column

[Bart1980]

Hi,

I am stuck on this one so any help is more than welcome :D

I have a column with text and I need to copy any account numbers to a new column. The account numbers vary so I have to use wildcards, but the other problem is that the account numbers can have 3 different patterns (hyphen (example 1), empty space between numbers (example 3)).

For example text ... 123-45678-90 (account number) ... text ... 123456789 (account number) ... text .. 1234 5678 9012 3456 (account number)... text.

I think you have to use the LIKE operator with wildcards but with the hypen and empty space + copy to a new column thingy is just a bit too difficult for me. The alternative is reading a bunch of records every day and mannualy copy paste them :s

 

[mumps]

Assuming your account numbers are in column A starting in row 1, place this formula in cell B1 and copy it down the column as far as there is data in column A.
=SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","")

 

[mikerickson]

Is the question you need answered
"is the given string an account number (in one of the three formats)?"
or is the question
"are the two given strings the same account number, perhaps in different formats?"

 

[Bart1980]

Is the question you need answered
"is the given string an account number (in one of the three formats)?"
or is the question
"are the two given strings the same account number, perhaps in different formats?"

The question needed to be answered is "is the given string in the text an account number (in one of the three formats)" if the answer is yes it needs to be copied to another column...

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you give us 6-8 varied 'dummy' data and the corresponding expected results?

My signature block below has a link for suggestions for posting data that we can not only see the layout of rows & columns, but we can also copy the data to test with.

 

[Bart1980]

Welcome to the MrExcel board!

Could you give us 6-8 varied 'dummy' data and the corresponding expected results?

My signature block below has a link for suggestions for posting data that we can not only see the layout of rows & columns, but we can also copy the data to test with.

Hello,

The addin doesn't seem to work, gives the error 91 ... With..

but her is an example of what it should do.

Column A:
[TABLE="width: 603"]
<colgroup><col></colgroup><tbody>[TR]
[TD]TEXT
[/TD]
[/TR]
[TR]
[TD]A1) received from 123-4567-89 'k Zag twee beren broodjes smeren
[/TD]
[/TR]
[TR]
[TD]A2) t Was een wonder 1234 5678 9102 boven wonder
[/TD]
[/TR]
[TR]
[TD]A3) dat die 123456789 beren smeren konden.
[/TD]
[/TR]
[TR]
[TD]A4) Hi hi hi, ha ha 987-6543-21 ha
[/TD]
[/TR]
[TR]
[TD]A5) k stond erbij en ST12 5467 8741 ik keek ernaar.
[/TD]
[/TR]
[TR]
[TD]A6) Ik zag twee apen VW11 5444 6698 wortelen schrapen
[/TD]
[/TR]
[TR]
[TD]A7) Ik zag twee apen 111-1111-11 tarwe rapen / nootjes rapen
[/TD]
[/TR]
[TR]
[TD]A8) Ik zag twee vlooien 252555548 mutsjes plooien

Column B (result)
[TABLE="width: 152"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ACCOUNT NUMBER
[/TD]
[/TR]
[TR]
[TD]B1) 123-4567-89
[/TD]
[/TR]
[TR]
[TD]B2) 1234 5678 9102
[/TD]
[/TR]
[TR]
[TD]B3) 123456789
[/TD]
[/TR]
[TR]
[TD]B4) 987-6543-21
[/TD]
[/TR]
[TR]
[TD]B5) ST12 5467 8741
[/TD]
[/TR]
[TR]
[TD]B6) VW11 5444 6698
[/TD]
[/TR]
[TR]
[TD]B7) 111-1111-11
[/TD]
[/TR]
[TR]
[TD]B8) 252555548
[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

The addin doesn't seem to work, gives the error 91 ... With..

Which Add-In (there are several)?
Where was the error?
What version of Excel are you using?


In any case, those samples should be sufficient this time.

I have written this as a user-defined function but could be just a macro that does the job if you want.

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function AccNo(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^| )([a-zA-Z]*\d[\d \-]*\d)(?=[^\d]|$)"
  If RX.Test(s) Then AccNo = RX.Execute(s)(0).SubMatches(1)
End Function

Excel 2010 32 bit
	A	B
1	TEXT	ACCOUNT NUMBER
2	received from 123-4567-89 'k Zag twee beren broodjes smeren	123-4567-89
3	t Was een wonder 1234 5678 9102 boven wonder	1234 5678 9102
4	dat die 123456789 beren smeren konden.	123456789
5	Hi hi hi, ha ha 987-6543-21 ha	987-6543-21
6	k stond erbij en ST12 5467 8741 ik keek ernaar.	ST12 5467 8741
7	Ik zag twee apen VW11 5444 6698 wortelen schrapen	VW11 5444 6698
8	no account number
9
10	Ik zag twee apen 111-1111-11 tarwe rapen / nootjes rapen	111-1111-11
11	Ik zag twee vlooien 252555548 mutsjes plooien	252555548
AccNo

Cell Formulas
Range	Formula
B2	=AccNo(A2)

 

[Bart1980]

Peter,

Thank you very much, I'll try it as soon as i get to work tomorrow!
Have a nice Sunday.

Kind regards,

Bart

PS Seeing your code makes me dazzle and realize that I am just a newbie at VBA :s