Home

Convert numbers to dollar value in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-772821%22%20slang%3D%22en-US%22%3EConvert%20numbers%20to%20dollar%20value%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772821%22%20slang%3D%22en-US%22%3E%3CP%3EI%20currently%20have%20a%20txt%20file%20which%20is%20pulling%20through%20our%20values%20as%20follows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E000000000000012730C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E000000000002403840D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20convert%20this%20to%20a%20dollar%20value%20where%20all%20C%20items%20are%20negative%20and%20D%20are%20positive.%2C%20and%20the%20last%203%20digits%20of%20the%20number%20are%20after%20the%20.%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e.%20the%20above%20should%20read%20-%2412.73%20and%20%242403.84%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBron%20Kroon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-772821%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772835%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20numbers%20to%20dollar%20value%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381837%22%20target%3D%22_blank%22%3E%40BronKroon%3C%2FA%3E%26nbsp%3B%20Try%20this%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DLEFT(A1%2C18)%2F1000*IF(RIGHT(A1%2C1)%3D%22D%22%2C1%2C-1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20result%20as%20currency.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772842%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20numbers%20to%20dollar%20value%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772842%22%20slang%3D%22en-US%22%3E%3CP%3EInge%20-%20you're%20a%20legend.%20Thanks%20so%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E
BronKroon
New Contributor

I currently have a txt file which is pulling through our values as follows: 

 

000000000000012730C

 

000000000002403840D

 

How do I convert this to a dollar value where all C items are negative and D are positive., and the last 3 digits of the number are after the . 

i.e. the above should read -$12.73 and $2403.84

 

Regards

Bron Kroon

2 Replies

@BronKroon  Try this

 

=LEFT(A1,18)/1000*IF(RIGHT(A1,1)="D",1,-1)

 

Format the result as currency.

Inge - you're a legend. Thanks so much

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