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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies