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
Highlighted
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
Highlighted

@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.