Home

Merge two separate cells in excel, that feature currency to create a range ($#-$##)

%3CLINGO-SUB%20id%3D%22lingo-sub-621897%22%20slang%3D%22en-US%22%3EMerge%20two%20separate%20cells%20in%20excel%2C%20that%20feature%20currency%20to%20create%20a%20range%20(%24%23-%24%23%23)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-621897%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20everyone%2C%20I%20am%20hoping%20someone%20might%20be%20able%20to%20help%20me.%20I%20have%20two%20columns%20denoting%20currency%20eg%20E1%3D%240%2C%20F1%3D%2469%20(converted%20to%20single%20decimal).%20I%20am%20attempting%20to%20merge%20the%20two%20together%2C%20and%20create%20a%20range%20(%240-%2469).%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20used%20the%20concatenate%20function%2C%20but%20the%20result%20I%20get%20is%3A%200-%2468.814*******.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20need%20to%20achieve%20the%20following%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E-%20numbers%20need%20to%20remain%20whole%20numbers%2C%20without%20decimals%20-%20both%20number%20values%20need%20to%20be%20expressed%20as%20%24%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E-%20the%20%22-%22%20needs%20to%20be%20added%2C%20without%20a%20space%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EDoes%20anyone%20know%20of%20a%20formula%20that%20may%20help%3F%20OR%20-%20is%20there%20something%20I%20need%20to%20change%20in%20cell%20format%20to%20achieve%20the%20result%20I%20am%20looking%20for%3F%20Thanks%20in%20advance%20-%20any%20insight%20or%20assistance%20would%20be%20greatly%20appreciated.%20%3A)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-621897%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-622120%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20separate%20cells%20in%20excel%2C%20that%20feature%20currency%20to%20create%20a%20range%20(%24%23-%24%23%23)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622120%22%20slang%3D%22en-US%22%3EHI%2C%20This%20should%20achieve%20what%20you%20want%20%3D%22%24%22%26amp%3BINT(E1)%26amp%3B%22-%24%22%26amp%3BROUND(F1%2C0)%3CBR%20%2F%3E%3CBR%20%2F%3ERich%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-622369%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20separate%20cells%20in%20excel%2C%20that%20feature%20currency%20to%20create%20a%20range%20(%24%23-%24%23%23)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-622369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259521%22%20target%3D%22_blank%22%3E%40Rich99%3C%2FA%3E%26nbsp%3B-%20thanks%20alot%20for%20that%20mate.%20It%20worked%20a%20charm!%3CBR%20%2F%3E%3CBR%20%2F%3E%3A)%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20don't%20mind%20be%20troubling%20you%20-%20would%20you%20be%20able%20to%20point%20out%20where%20I%20went%20wrong%20with%20the%20approach%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20trying%20to%20challenge%20myself%20to%20tackle%20things%20like%20this%20in%20excel%2C%20and%20make%20sure%20I%20learn%20each%20time%20I%20encounter%20something%20like%20this.%20Any%20quick%20insights%20you%20can%20offer%20would%20be%20greatly%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3E%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646557%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20two%20separate%20cells%20in%20excel%2C%20that%20feature%20currency%20to%20create%20a%20range%20(%24%23-%24%23%23)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646557%22%20slang%3D%22en-US%22%3ESorry%20for%20the%20late%20reply%2C%20The%20main%20issue%20is%20that%20currency%20is%20a%20format%20issue%2C%20i.e.%20you%20just%20enter%20a%20number%20such%20as%200%20and%20view%20it%20as%20%240%20hence%20no%20%24%20symbol%20when%20you%20copy%20it.%20You%20can%20Round%20numbers%20with%20Concatenate%2C%20CONCATENATE(E1%2CROUND(F1%2C0))%20but%20i%20suspect%20your%20%2468.814%20was%20not%20saved%20as%20a%20number%20as%20the%20result%20you%20got%20when%20concatenating%20it%20included%20a%20%24%20symbol.%3C%2FLINGO-BODY%3E
Ben83
New Contributor

Hi everyone, I am hoping someone might be able to help me. I have two columns denoting currency eg E1=$0, F1=$69 (converted to single decimal). I am attempting to merge the two together, and create a range ($0-$69).

I have used the concatenate function, but the result I get is: 0-$68.814*******.

I need to achieve the following:

- numbers need to remain whole numbers, without decimals - both number values need to be expressed as $

- the "-" needs to be added, without a space

Does anyone know of a formula that may help? OR - is there something I need to change in cell format to achieve the result I am looking for? Thanks in advance - any insight or assistance would be greatly appreciated. :)

3 Replies
HI, This should achieve what you want ="$"&INT(E1)&"-$"&ROUND(F1,0)

Rich

@Rich99 - thanks alot for that mate. It worked a charm!

:)

If you don't mind be troubling you - would you be able to point out where I went wrong with the approach?

I'm trying to challenge myself to tackle things like this in excel, and make sure I learn each time I encounter something like this. Any quick insights you can offer would be greatly appreciated.

:)

Sorry for the late reply, The main issue is that currency is a format issue, i.e. you just enter a number such as 0 and view it as $0 hence no $ symbol when you copy it. You can Round numbers with Concatenate, CONCATENATE(E1,ROUND(F1,0)) but i suspect your $68.814 was not saved as a number as the result you got when concatenating it included a $ symbol.
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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies