Home

Struggling with Correct Formula/Function to Find & Replace in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-567123%22%20slang%3D%22en-US%22%3EStruggling%20with%20Correct%20Formula%2FFunction%20to%20Find%20%26amp%3B%20Replace%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-567123%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20find%20the%20correct%20formula%20to%20apply%20to%20a%20regualry%20used%20Spread%20Sheet%2C%20where%20i%20can%20apply%20a%20Formula%20that%20can%20find%20a%20value%20in%20Column%20P%20(Unit%20of%20Measure)%20%26amp%3B%20conditionally%20Replace%20the%20Value%20in%20Column%20J%20(Product%20Code)%2C%20but%20where%20several%20different%20product%20codes%20are%20used%20%26amp%3B%20require%20this%20application.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20like%20with%20direct%20reference%20to%20my%20specific%20Spreadsheet%3A%3C%2FP%3E%3CP%3EIF%20Column%20P%20%3D%20MT%2C%20Replace%20Column%20J%20(with%20original%20value%20%2BM%20(eg.%20ODV050%20with%20ODV050M))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnable%20to%20set%20formula%20to%20recognize%20the%20different%20Product%20Codes%20%26amp%3B%20set%20the%20'M'%20definition%20across%20multiple%20codes%20(ODV040%20-%20ODV040M%2C%20ODV050%20-%20ODV050M%2C%20ODV065%20-%20ODV065M%2C%26nbsp%3BODV090%20-%20ODV090M%20%26amp%3B%26nbsp%3BODV100%20-%20ODV100M)%20when%20Column%20P%20%3D%20MT.%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-567123%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-567895%22%20slang%3D%22en-US%22%3ERe%3A%20Struggling%20with%20Correct%20Formula%2FFunction%20to%20Find%20%26amp%3B%20Replace%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-567895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342089%22%20target%3D%22_blank%22%3E%40MilesHoffmann%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20you%20add%20another%20Column%20labeled%20Product%20Code%2C%20such%20that%20the%20formula%20in%20Q2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(P2%3D%22MT%22%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EJ2%26amp%3BLEFT(P2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EJ2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20the%20foregoing%20formula%20in%20the%20attached%20version%20of%20your%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
MilesHoffmann
Occasional Visitor

I'm trying to find the correct formula to apply to a regualry used Spread Sheet, where i can apply a Formula that can find a value in Column P (Unit of Measure) & conditionally Replace the Value in Column J (Product Code), but where several different product codes are used & require this application.

 

Something like with direct reference to my specific Spreadsheet:

IF Column P = MT, Replace Column J (with original value +M (eg. ODV050 with ODV050M))

 

Unable to set formula to recognize the different Product Codes & set the 'M' definition across multiple codes (ODV040 - ODV040M, ODV050 - ODV050M, ODV065 - ODV065M, ODV090 - ODV090M & ODV100 - ODV100M) when Column P = MT.

Any assistance would be greatly appreciated.

 

1 Reply

@MilesHoffmann 

I suggest you add another Column labeled Product Code, such that the formula in Q2, copied down rows, is: 

=IF(P2="MT",
J2&LEFT(P2),
J2)

See the foregoing formula in the attached version of your file. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 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
13 Replies