I need to create a macro or VBA project in Excel 2003 that will look at the current worksheet column and select a specific alphanumeric or numeric text string that matches this format (V#####, or #####, or V########) than cut and paste just that string to a different empty column to the right on the same row and do this for all rows in the worksheet.
Example of current data: The highlighted strings would be cut and placed into a separate column to the right of this column but in the same row.
| BUFFALO, NY 142072504 |
| GARBEN, TIM (9992) x 9992 |
| 2311 ERNEST STREET x (Alternate) |
| (Corporate) |
| JACKSONVILLE, FL 32204 |
| Hospital Santa Cruz (V0014000000002) (41) 312-3000 x V0014000000002 |
| Av. Batel, #1889 x (Alternate) |
| (Corporate) |
| Curitiba, PR |
| Restaurante Mabu (V0014000000010) (41) 347-1001 x V0014000000010 |
| Manoel Valdomiro de Macedo, #2609 x (Alternate) |
| (Corporate) CIC |
| Curitiba, PR |
| t (V0015000000004) (3__)___-____ x V0015000000004 |
| t (3__)___-____ x |
| (Alternate) |
| (Corporate) |
| t, NH 9 |
| Brazil |
| Answering Services, Inc (V0018000000001) 248-353-5890 x V0018000000001 |
|
Like this......
GARBEN, TIM (9992) x |
9992 |
| 2311 ERNEST STREET x (Alternate) | |
| (Corporate) | |
| JACKSONVILLE, FL 32204 | |
| Hospital Santa Cruz (V0014000000002) (41) 312-3000 x | V0014000000002 |
| Av. Batel, #1889 x (Alternate) | |
| (Corporate) | |
| Curitiba, PR | |
| Restaurante Mabu (V0014000000010) (41) 347-1001 x | V0014000000010 |
| Manoel Valdomiro de Macedo, #2609 x (Alternate) | |
| (Corporate) CIC | |
| Curitiba, PR | |
| t (V0015000000004) (3__)___-____ x | V0015000000004 |
| t (3__)___-____ x | |
| (Alternate) | |
| (Corporate) | |
| t, NH 9 | |
| Brazil | |
| Answering Services, Inc (V0018000000001) 248-353-5890 x | V0018000000001 |
If anyone can help me with this I'd really appreciate it.
Thanks,
Chuck
Macro in Excel that will look at current worksheet column and select a specific string and...
DigiVix
You want to use regular expressions. These are a way to search text that conforms to certain search patterns. Very powerful and it's exactly what your looking for.
This will give you the basics on how to use Regular Expressions in vba.
http://dsmyth.blogspot.com/2006/01/regular-expressions-in-vba.html
To know how to create the patterns that match what you need check out
http://www.regexbuddy.com/
There is some good introduction articles there that will get you started. I highly recommend you buying the RegexBuddy software, it is a valuable tool and it's value for money.