CODEWITHSUNDEEP
excelexcel-formula
Saurabh
Saurabh

Reputation: 107

Extract specific string from Excel cell using a identifier | MS Excel

Need to extract a particular string from a excel cell using excel formula.

Input string:

CREATE TABLE IF NOT EXISTS ${SCHEMA_NM}.beneficiary_enriched STORED AS ORC tblproperties("orc.compress"="SNAPPY")   AS  SELECT DISTINCT source, individual_analytics_identifier,    preferred_proxy_id, demogr_first_name,  demogr_last_name,   birth_date, gender, zip_code,   state,  coverage_effective_date,    coverage_expiration_date    FROM    ${SCHEMA_NM}.beneficiary_decrypted;

Output string:

col1 col2
${SCHEMA_NM}.beneficiary_enriched ${SCHEMA_NM}.beneficiary_decrypted

Want to extract table name from create statement. col1 has 'new table name' and col2 has 'from table name' starting from '$'

Formula i tried:(not working in all cases)

For getting new table name:

=MID(A1,SEARCH("$",A1)+1,(LEN(A1)-(SEARCH("$",A1)+1))-(LEN(A1)-SEARCH(" ",A1,((LEN(LEFT(A1,SEARCH("$",A1)-1))+1)))))```

Help me get the correct output.

Upvotes: 1

Views: 215

Answers (1)

JvdV
JvdV

Reputation: 75990

  • Assuming the only dollar signs are those to indicate your desired substrings.

Excel ms365 BETA-Channel:

enter image description here

Formula in B4:

="$"&TOROW(TAKE(TEXTSPLIT(A1,"$",{" ",";"}),,-1),3)

Note: To assert we only split on dollar signs of interest we could add a space inside: TEXTSPLIT(A1," $",{" ",";"}). That would leave other dollar signs alone.

Excel ms365:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,";"," ")," ","</s><s>")&"</s></t>","//s[starts-with(.,'$')]"))

Excel 2013 (or higher):

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A1,";"," ")," ","</s><s>")&"</s></t>","//s[starts-with(.,'$')]"),COLUMN(A1))

And drag right.

Note: For both the FILTERXML() options we allready assert that the 1st character is a dollar sign. If you want to be more explicit you can add the curly brackets inside the xpath for example: "//s[starts-with(.,'${')]"

Upvotes: 1

Related Questions