Community

Spreadsheet-like, I am assembing my tab-delimited database. Here is a crude layout of part of it:

^1stRndmWORD+TAB……RndmWORD+TAB……(123)+TAB……RndmWORD+TAB……RndmWORD+TAB……(123)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……(456)+TAB……RndmWORD+TAB……RndmWORD+TAB……(123)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……(123)+TAB……RndmWORD+TAB……RndmWORD+TAB……(456)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……(123)+TAB……RndmWORD+TAB……RndmWORD+TAB……(789)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……(789)+TAB……RndmWORD+TAB……RndmWORD+TAB……(789)+TAB……etc.

"Rndm" means really that these are random (unique) words/strings, and thus I have nothing consistent to link to.

But the columns are fixed; and the parenthetical items — which will always appear at these fixed tab positions — stand for 3 specific telephone area codes: I've labeled them (123), (456) and (789) in this example; note the presence of two area codes on each line/record.

It's the first occurence of an area code on each line that I wish to operate on, and here is what I'm hoping to quickly do:

Find the first area code on each line
Mark those lines
On the marked lines, insert a two-letter code unique to that particular area code right after the 1stRndmWORD+TAB on that line.

EXAMPLE WHEN FINISHED:

^1stRndmWORD+TAB……RndmWORD+TAB……QL……(123)+TAB……RndmWORD+TAB……RndmWORD+TAB……(123)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……PK……(456)+TAB……RndmWORD+TAB……RndmWORD+TAB……(123)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……QL……(123)+TAB……RndmWORD+TAB……RndmWORD+TAB……(456)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……QL……(123)+TAB……RndmWORD+TAB……RndmWORD+TAB……(789)+TAB……etc.
^1stRndmWORD+TAB……RndmWORD+TAB……RW……(789)+TAB……RndmWORD+TAB……RndmWORD+TAB……(789)+TAB……etc.

The conflict I'm having relates to the lines that have two different area codes on the same line.

For example, when I'm looking for (123)+TAB, the line
^1stRndmWORD+TAB……RndmWORD+TAB……(456)+TAB……RndmWORD+TAB……RndmWORD+TAB……(123)+TAB……etc.

finds the (123)+TAB also, when I wouldn't want that record marked because its lead area code is (456)+TAB.

(456)+TAB is also taking (456)+TAB
(789)+TAB is also taking (789)+TAB

and so on. I know I'm not great at being clear with these examples, so please feel free to discipline my thinking. I just need to end up with these three 2-letter codes — QL, PK, and RW — on their lead area codes.

Thanks guys!

Skye

For the "123" part, try using the Replace command with the following regular expression:

Find what: ^\([^(]+\)\((123)\)
Replace with: \1QL \2

Or, if you have chosen to use POSIX syntax:

Find what: ^([^(]+)(\(123\))
Replace with: \1QL \2

Note that [^(] matches everything except "(", so ^[^(]+ matches everything from the start of the line to the first "(". Lookup regular expressions in the online help for more information. You might also like to have a look at "Mastering Regular Expressions" by J.E.F Friedl (O'Reilly & Associates, 2002).

Keith MacDonald
Helios Software Solutions

Has that answered you?
What do the dots following the TABs mean?
Try:
Find:
^\([^\t]+\t[^\t]+\t[^(]*\)(123)
Rep
\1QL\t(123)

Hi again Ed,

Yes, I replied gratefully to Keith's post but my reply was deleted. It works beautifully, as I had remarked in my reply.

bbadmin wrote:You might also like to have a look at "Mastering Regular Expressions" by J.E.F Friedl (O'Reilly & Associates, 1997).

Don't make the same mistake as me. There's a new version of this book that just came out...like two weeks after I bought the old.

Thanks Jeffy, I've updated the publication date to 2002 in my original posting.

Keith MacDonald
Helios Software Solutions

1) press SHIFT+F10 then B (to put you in Block Select mode)
2) select the first half column of your data, making sure it captures all the zipcodes only on the left side and none of the zipcodes on the right side
3) press F8 (search and replace)
4) type "(123)" in Find-What
5) type "AL (123)" in Replace-With
6) check Selected-text
7) hit Replace-All
8 ) repeat for other area codes