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 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 – steeldriver Mar 14 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 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 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 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 at 14:14
  • Please explain this sed "s/\/[0-9]\{3\}.*//g" @Praveen Kumar BS – Ibrahim A Mar 20 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 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 at 13:13
  • 1
    you just need to escape the "!" => "\!" – Jonah Mar 18 at 18:39
  • 1
    Or set +H in the interactive bash session (not needed in scripts). – Kusalananda Mar 18 at 18:48
  • Escaping the ! causes the output to show the ! symbol. – Ibrahim A Mar 20 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.