SOLVED
Home

Subract data in one column from data in another column

%3CLINGO-SUB%20id%3D%22lingo-sub-707890%22%20slang%3D%22en-US%22%3ESubract%20data%20in%20one%20column%20from%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707890%22%20slang%3D%22en-US%22%3E%3CP%3EColumn%20A%20has%20stock%20CUSIP%2C%20Column%20B%20has%20description%20plus%20CUSIP.%20I%20want%20to%20remove%20the%20CUSIP%20from%20Column%20B%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EColumn%20A%3A%20%26nbsp%3BG1151C101%3C%2FP%3E%3CP%3EColumn%20B%3A%20%26nbsp%3BACCENTURE%20PLC%20CLS%20A%20USD0.0000225%20ACN%20G1151C101%3C%2FP%3E%3CP%3EObjective%3A%20remove%26nbsp%3BG1151C101%20from%20Column%20B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20this%20be%20done%2C%20and%20if%20so%2C%20how%3F%26nbsp%3B%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-707890%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707909%22%20slang%3D%22en-US%22%3ERe%3A%20Subract%20data%20in%20one%20column%20from%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F76343%22%20target%3D%22_blank%22%3E%40David%20Wess%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20it%20always%20at%20the%20right%2C%20as%20variant%20you%20may%20add%20to%20column%20C%3C%2FP%3E%0A%3CPRE%3E%3DLEFT(B1%2CLEN(B1)-LEN(A1)-1)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707967%22%20slang%3D%22en-US%22%3ERe%3A%20Subract%20data%20in%20one%20column%20from%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707967%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20seem%20to%20get%20it%20to%20work%20-%20could%20I%20impose%20upon%20you%20to%20input%20it%20into%20my%20spreadsheet%20(attached)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707982%22%20slang%3D%22en-US%22%3ERe%3A%20Subract%20data%20in%20one%20column%20from%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F76343%22%20target%3D%22_blank%22%3E%40David%20Wess%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDavid%2C%20you%20have%20a%20lot%20of%20spaces%20at%20the%20end%20of%20the%20text.%20I%20removed%20them%20by%20TRIM%3C%2FP%3E%0A%3CPRE%3E%3DLEFT(TRIM(F3)%2CLEN(TRIM(F3))-LEN(TRIM(E3))-1)%3C%2FPRE%3E%0A%3CP%3Esee%20in%20G3%20attached.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-708154%22%20slang%3D%22en-US%22%3ERe%3A%20Subract%20data%20in%20one%20column%20from%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-708154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20VERY%20much%2C%20sir%2C%20greatly%20appreciated!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-708393%22%20slang%3D%22en-US%22%3ERe%3A%20Subract%20data%20in%20one%20column%20from%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-708393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F76343%22%20target%3D%22_blank%22%3E%40David%20Wess%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
David Wess
New Contributor

Column A has stock CUSIP, Column B has description plus CUSIP. I want to remove the CUSIP from Column B:

 

Example:

Column A:  G1151C101

Column B:  ACCENTURE PLC CLS A USD0.0000225 ACN G1151C101

Objective: remove G1151C101 from Column B

 

Can this be done, and if so, how?  Thanks.

 

 

 

 

5 Replies

@David Wess 

 

It it always at the right, as variant you may add to column C

=LEFT(B1,LEN(B1)-LEN(A1)-1)

 

@Sergei Baklan 

 

I cannot seem to get it to work - could I impose upon you to input it into my spreadsheet (attached)?

 

Thanks!

Solution

@David Wess 

David, you have a lot of spaces at the end of the text. I removed them by TRIM

=LEFT(TRIM(F3),LEN(TRIM(F3))-LEN(TRIM(E3))-1)

see in G3 attached.

 

@Sergei Baklan 

 

Thank you VERY much, sir, greatly appreciated!!

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies