Use awk/sed to remove everything but matching pattern in a specific column

Question

I have a file that looks like this:

Text1 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text1
Text2 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text2
Text3 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text3

The "something", something,else, and foo are random letters/spaces/commas The AAxxxxxxx is what I want to match. the X's are numbers, and it's always an array of 7 numbers 0-9 so example AA0000001 or AA9999999. I want to extract just the AAxxxxxxx part from column 2 so my output reads:

Text1 AAxxxxxxx,AAxxxxxxx text1
Text2 AAxxxxxxx,AAxxxxxxx text2
Text3 AAxxxxxxx,AAxxxxxxx text3

Example input

Text1 somethingAA0123456something,elseAA6543210foo text1
Text2 somethingAA1234567something,elseAA7654321foo text2
Text3 somethingAA2345678something,elseAA8765432foo text3

Desired output

Text1 AA0123456,AA6543210 text1
Text2 AA1234567,AA7654321 text2
Text3 AA2345678,AA8765432 text3

Edit: A few lines contain more than 2 "AAxxxxxxx" segments for example

Input

Text1 somethingAAxxxxxxxsomething,elseAAxxxxxxxfooblahAAxxxxxxx^blahblahAAxxxxxxx text1
Text2 somethingAAxxxxxxxsomething,elseAAxxxxxxxfooblahAAxxxxxxx^blah text2
Text3 somethingAAxxxxxxxsomething,elseAAxxxxxxxfoo text3

Desired Output

Text1 AA0123456,AA6543210,AA1231252,AA1256712 text1
Text2 AA1234567,AA7654321,AA1926572 text2
Text3 AA2345678,AA8765432 text3

Answer 1

sed can do that. We can use 4 matching groups to find the prefix, suffix and two IDs in the middle.

Code:

sed -rn 's/([^ ]+) .*(AA[0-9]{7}).*(AA[0-9]{7}).* ([^ ]+)/\1 \2,\3 \4/p' file1

Test Data:

Text1 somethingAA0123456something,elseAA6543210foo text1
Text2 somethingAA1234567something,elseAA7654321foo text2
Text3 somethingAA2345678something,elseAA8765432foo text3

Results:

Text1 AA0123456,AA6543210 text1
Text2 AA1234567,AA7654321 text2
Text3 AA2345678,AA8765432 text3

Answer 2

perl approach. Similar to the sed approach. Test the line against a lengthy regular expression, capturing the relevant desired parts of the line into $1, $2, $3, $4. Construct an answer in $_ so that it then gets printed thanks to the '-p' flag.

$ perl -pe 'if(/^(Text\d+) .*(AA\d{7}).*(AA\d{7}).* (.*)/){$_="$1 $2,$3 $4$/"}' file1
Text1 AA0123456,AA6543210 text1
Text2 AA1234567,AA7654321 text2
Text3 AA2345678,AA8765432 text3
$

Answer 3

awk solution:

$ a="Text1 somethingAA0123456something,elseAA9876543foo text1"
$ awk -F"[ ,]" '{match($2,/(AA[0-9]{7})/,a);match($3,/(AA[0-9]{7})/,b);print $1,a[1],",",b[1],$NF}' <<<"$a"
Text1 AA0123456,AA9876543 text1

Also this works:

$ awk '{match($0,/(\w+\s)(\w+)(\w\w[0-9]{7})(\w+,\w+)(\w\w[0-9]{7})(\w+\s)(\w+)/,a);print a[1],a[3],",",a[5],a[7]}' <<<"$a"

UPDATE
For your new requirements and with GNU awk you can use something like this:

$ echo "$b"
Text1 somethingAA1111111something,elseAA2222222fooblahAA3333333^blahblahAA4444444 text1
Text2 somethingAA1111111something,elseAA7777777fooblahAA5454545^blah text2
Text3 somethingAA1111111something,elseAA2222222foo text3

$ awk '{gsub(/(AA[0-9]{7})/," & ",$2)}1' <<<"$b" |awk '{printf("%s ",$1);for (i=2;i<NF;i++) {if($i ~ /AA[0-9]+/) printf("%s%s",$i,(i==NF-1)?" ":",")}}{printf(" %s\n",$NF)}'
Text1 AA1111111,AA2222222,AA3333333,AA4444444  text1
Text2 AA1111111,AA7777777,AA5454545, text2
Text3 AA1111111,AA2222222, text3

Only pitfall that there is an extra comma in some records after the last AAXXXXXXX . Hope this is not a big problem.

This solution combines two awk. Ths first awk transform each line by injecting a space before and after each AAXXXXXXX found:

$ echo "$a"
Text2 somethingAA1234567something,elseAA0987654fooblahAA3333333^blah text2
$ awk '{gsub(/(AA[0-9]{7})/," & ",$2)}1' <<<"$a"
Text2 something AA1234567 something,else AA0987654 fooblah AA3333333 ^blah text2

This transformed records are then fed to second awk which prints the first field, the last field and the intermediate fields that match the pattern AAXXXXXXX

Answer 4

Bash Solution for the updated request:

echo "$b"
#Output
Text1 somethingAA1111111something,elseAA2222222fooblahAA3333333^blahblahAA4444444 text1
Text2 somethingAA1111111something,elseAA7777777fooblahAA5454545^blah text2
Text3 somethingAA1111111something,elseAA2222222foo text3

while IFS=" " read -r f1 f2 f3;do 
  g=($(grep -Po 'AA[0-9]{7}' <<<"$f2"));
  out=$(printf '%s,%s,%s\n' "$f1" "${g[*]}" "$f3");
  echo "$out" |sed 's/,/\x00/g; s/ /,/g; s/\x00/ /g';
done <<<"$b"
#Output
Text1 AA1111111,AA2222222,AA3333333,AA4444444 text1
Text2 AA1111111,AA7777777,AA5454545 text2
Text3 AA1111111,AA2222222 text3

Answer 5

Perl

perl -pale '$_ = join $", $F[0], join(",", $F[1] =~ /AA\d{7}/g), @F[2..$#F]' yourfile

Bash

The use of cat is intentional here, as we don't want to destroy the positional parameters ($1, $2, ..., $#) and hence running the while-loop in a subshell.

cat yourfile |
while read -r f1 f2 rem; do
   set -- "$f1" "$(printf '%s\n' "$f2" | grep -oP 'AA\d{7}' | paste -sd,)" "$rem"
   printf '%s\n' "$*"
done

Sed

sed -e '
   s/[^ ]*[ ]*/&\
\
/
   s/AA[0-9]\{7\}/\
&\
/g

   :loop
      s/\nAA[0-9]\{7\}\(\n\)/\1&/
      s/\n\n.*\(\n\n\)/\1/
      s/\(\n\n\)\(AA[0-9]\{7\}\)\n/\2,\1/
   /\nAA[0-9]\{7\}\n/bloop

   s/,\n\n[^ ]*//
' yourfile