ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

2 Q & A posts tagged with CONCATENATE

Sun
Apr 26
2009

Excel: is there a way to emulate the lower function without using lower()?

Excel: is there a way to emulate the lower function without using lower()??

note: it has to work for cells with 2 or more words


Answer

I think there is, but it is going to be too long and time consuming
If your cell is in A1, paste this in B1
=IF(CODE(MID( $A$1,COLUMN()-1,1))=32," ",CHAR( CODE(MID( $A$1,COLUMN( )-1,1))-32))

Then drag it to right to cover all letters of that text
After that, you can easily use CONCATENATE or the & symbol to assemble them into one cell text, like this
=B1&C1&D1&D1
or
=CONCATENATE(B1,C1,D1, E1)

until you reach the end


good luck


VBAXLMan

Sunday 4/26/2009 3:23:34 AM
Tue
Dec 12
2006

This is regarding Concatenate function in Excel:?

I am trying to concatenate a Currency value ( $ 233,456,389) with a

percentage ( 74 %) in Excel using this function :


=CONCATENATE("$ ", TRUNC(B313)," @ ",TRUNC(C314)," % ")



I would like to have it as $ 233,456,389 @ 74%

Instread I am getting $ 233456389 @ 74% (currency value without commas)

Can someone help me in getting the commas in the currency display ?


Answer

Add this to the TRUNC function
TEXT(B313 ,"###,###")
Means the TRUNC would be
TRUNC(TEXT( B313, "###,###"))
and your final function will be

=CONCATENATE("$ ", TRUNC(TEXT( B313, "###,###"))," @ ",TRUNC(C314)," % ")

Enjoy my profile, I am the VBAXLMan

Tuesday 12/12/2006 11:03:16 PM

Tags

History