I have a file called LoginExcInternal.txt which has data that looks like the following:

Name1   OpenFin/
Name2   OpenFin/
Name3   Chrome/

These two columns are being separated by tabs.

I need to output this to another file which will replace both OpenFin and Chrome with =VLOOKUP('Mar 19'!A4,'User-Firm'!A2:B1999,2,FALSE)

The Mar 19 should always show the month and year for last Monday.

This is what I have so far:

z="$(date -d 'last Sunday - 6 day' '+%b %y')" && sed 's/OpenFin.*/=VLOOKUP('$z'!A4,'User-Firm'!A2:B1999,2,FALSE)/g;s/Chrome.*/=VLOOKUP('$z'!A4,'User-Firm'!A2:B1999,2,FALSE)/g' LoginExcInternal.txt > Test2.txt

This gives me the error sed: -e expression #1, char 24: unterminateds' command`.

Desired Output:

Name1   =VLOOKUP('Mar 19'!A4,'User-Firm'!A2:B1999,2,FALSE)
Name2   =VLOOKUP('Mar 19'!A4,'User-Firm'!A2:B1999,2,FALSE)
Name3   =VLOOKUP('Mar 19'!A4,'User-Firm'!A2:B1999,2,FALSE)

Mar 19 should have the apostrophes, so it should look like 'Mar 19'

  • 2
    A summary of the desired output (given the sample input) would be nice for Answerers to be able to quickly compare their results; thanks!
    – Jeff Schaller
    Mar 14 '19 at 19:20
  • 2
    You have a typo z=$"(...)" in place of z="$(...)" - also the space around ' $z ' will need to be removed I think Mar 14 '19 at 19:20
  • I addition to what steeldriver said, you also have other quoting issues. A single quoted string can never contain single quotes. Please consider using shellcheck.net
    – Kusalananda
    Mar 14 '19 at 19:22
  • I made the edits along with the desired output. The single quotes show up like that which is why I posted it in this way as the final output should be in that format.
    – Ibrahim A
    Mar 14 '19 at 19:35
  • If you're using GNU sed or equivalent save a bunch of duplication by using one substitution with "alternation" in the pattern: 's/(OpenFin|Chrome).*/..etc../'. (You'll need to add sed flag -E to avoid escaping parens and vert pipe).
    – B Layer
    Mar 15 '19 at 2:12

Command:sed -E "s/Chrome|OpenFin/=VLOOKUP\('Mar 19'\!A4,'User-Firm'\!A2:B1999,2,FALSE\)/g" filename|sed "s/\/[0-9]\{3\}.*//g"


sed -E "s/Chrome|OpenFin/=VLOOKUP\('Mar 19'\!A4,'User-Firm'\!A2:B1999,2,FALSE\)/g" 
 filename|sed "s/\/[0-9]\{3\}.*//g"

Name1   =VLOOKUP('Mar 19'!A4,'User-Firm'!A2:B1999,2,FALSE)
Name2   =VLOOKUP('Mar 19'!A4,'User-Firm'!A2:B1999,2,FALSE)
Name3   =VLOOKUP('Mar 19'!A4,'User-Firm'!A2:B1999,2,FALSE


  • Modified a little bit to this and works like a charm, thanks! z="$(date -d 'last Sunday - 6 day' '+%b %y')" && sed -E "s/Chrome.*|OpenFin.*/=VLOOKUP\('$z'\!A4,'User-Firm'\!A2:B1999,2,FALSE\)/g" LoginExcInternal.txt | sed "s/\/[0-9]\{3\}.*//g" >test.txt
    – Ibrahim A
    Mar 20 '19 at 14:14
  • Please explain this sed "s/\/[0-9]\{3\}.*//g" @Praveen Kumar BS
    – Ibrahim A
    Mar 20 '19 at 15:15
  • This is simplified and works: sed -E "s/Chrome.*|OpenFin.*/=VLOOKUP\('$(date -d 'last Sunday - 6 day' '+%b %y')'\!A4,'User-Firm'\A2:B1999,2,FALSE\)/g" LoginExcInternal.txt
    – Ibrahim A
    Mar 20 '19 at 15:41

without sed:

str="=VLOOKUP('$(date -d 'last Sunday - 6 day' '+%b %y')'";
str+="$(printf "\\$(printf '%03o' '33')")";
str+="$(printf "\\$(printf '%03o' '33')")";
while read name ignored;do 
    ((${#name}))&&echo "${name}    ${str}";

with sed:

sed "s/OpenFin.*$\|Chrome.*$/${str}/g" urfile.txt

this should work fine

  • The first one just gives me an error saying -bash: !A4,'User: event not found and the second only gives me the first column without adding the string I need on the second column.
    – Ibrahim A
    Mar 18 '19 at 13:13
  • 1
    you just need to escape the "!" => "\!"
    – Yunus
    Mar 18 '19 at 18:39
  • 1
    Or set +H in the interactive bash session (not needed in scripts).
    – Kusalananda
    Mar 18 '19 at 18:48
  • Escaping the ! causes the output to show the ! symbol.
    – Ibrahim A
    Mar 20 '19 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.