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
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies