Home

Excel Replacment

%3CLINGO-SUB%20id%3D%22lingo-sub-450221%22%20slang%3D%22en-US%22%3EExcel%20Replacment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450221%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20file%20which%20has%20a%20list%20of%20angles%20which%20have%20been%20imported%20to%20excel%20as%20327.25.36(degrees%2Cminutes.seconds)%3C%2FP%3E%3CP%3EI%20want%20to%20change%20all%20the%20angles%20in%20the%20file%20to%20be%20in%20the%20format%20327.2536%20as%20the%20software%20that%20reads%20the%20data%20expects%20this%20format%3C%2FP%3E%3CP%3ECan%20anyone%20help%20%3F%3C%2FP%3E%3CP%3EWendy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-450221%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-451017%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Replacment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-451017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320699%22%20target%3D%22_blank%22%3E%40Wendy_2464%3C%2FA%3E%20%2C%20you%20may%20transform%20by%3C%2FP%3E%0A%3CPRE%3E%3DREPLACE(A1%2CAGGREGATE(14%2C6%2C1%2F%20(MID(A1%2C%20ROW(1%3A14)%2C1)%3D%22.%22)%20*%20ROW(1%3A14)%2C1)%2C1%2C)%3C%2FPRE%3E%0A%3CP%3EAGGREGATE%20finds%20the%20position%20of%20last%20dot%2C%20when%20REPLACE%20it%20on%20nothing.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Wendy_2464
Occasional Visitor

I have a file which has a list of angles which have been imported to excel as 327.25.36(degrees,minutes.seconds)

I want to change all the angles in the file to be in the format 327.2536 as the software that reads the data expects this format

Can anyone help ?

Wendy

 

1 Reply

@Wendy_2464 , you may transform by

=REPLACE(A1,AGGREGATE(14,6,1/ (MID(A1, ROW(1:14),1)=".") * ROW(1:14),1),1,)

AGGREGATE finds the position of last dot, when REPLACE it on nothing.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies