Community

Hi all,

I have a comma delimited text file with rows like:

"txtLastName","txtFirstName","chrSSN","txtPhoneNumber","vchrAddress1","vchrtxtAddress2","chrCity","chrState","vchrHomeAddressZipCode"
"Allen","Arnold","000-00-0000","555-555-1234","123 Indian Rd.",,"Cityville","CA",12345

--

So far...

the expression i have wrote is: [0-9][0-9][0-9]\-[0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]
it finds phone numbers in a comma delimited .txt file like 123-456-7890

I think I also need to include the quotes on the phone number too, but i don't know how. I have looked for regex resources high and low to find out...

i want to seperate the areacode from the rest of the number and change the text file so if it said:

"Allen","Arnold","000-00-0000","555-555-1234","123 Indian Rd.",,"Cityville","CA",12345

I could use regex to find and replace the phone number so it would become:

"Allen","Arnold","000-00-0000","555", "555-1234","123 Indian Rd.",,"Cityville","CA",12345

--

Please help!! This text file is giving me a sever headache - it is 7000+ records long!!!

Sincerely,
-Josh Tiner
jtiner@tampabay.rr.com

If the first telephon-number is always filled with zeros and should not be converted a possible solution would be:

search: 0000","\([0-9]+\)-\([0-9-]+\)"
replace: 0000","\1","\2"

Explanation: Search for the literal text 0000"," followed by some digits and mark these as a group. This first group should be the area code. then comes a minus sign followed by any digits and minus signs (!) until a " is reached. all these digits excluding the quotation are marked as a second group.

Regular expressions should be checked, of course. And this is the Not-Posix syntax! Check this in your settings. It is the default.

If the first telephone-number is not always filled with zeros, replace 0000 with \([0-9]+\) in the search string. The replace string should then be \1","\2","\3"

If the first telephone number should be converted too, just leave out the 0000", part in both strings.

HTH