maintain format currency and

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Maintain format of currency and or date whent cocantenating data

Maintain format of currency and or date whent cocantenating data

resolvedResolved · Low Priority · Version 2003

John has attended:
Presentation Skills course
Excel Advanced course

Maintain format of currency and or date whent cocantenating data

maintain format of currency and or date whent cocantenating data within a text string (for "lazy" report writing)

RE: maintain format of currency and or date whent cocantenating

Hello John,

As I thought, Concatenate can only handle text but you can "translate" your currency and date data by using the Text function which combines a reference to the cell which contains the data with a currency / date picture (such as we use in Custom formatting) enclosed in " " to make it text.

Please see attached the test file I have put together for you.

C4 contains the main Concatenate function, the currency formatted data is in A6 with its Text function (referred to in the main formula) in C6. The formatted dates are in A8 and A10 with their Text functions in C8 and C10 respectively.

I leave it to your own judgement to decide if this solution still constitutes "lazy" report writing!

Kind regards,

Ruth

Attached files...

Concatenation Formatting Test.xls

RE: maintain format of currency and or date whent cocantenating

Many thanks Ruth

many years of "lazy" report writing ahead for me (if I can find the customers)!

all the best

John F


 

Excel tip:

Create own ribbon tab - Excel 2010

a. In Excel click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the New Tab button (below the list of tabs on the right hand side of the dialog box)
e. Select the New Tab (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
f. Select the New Group (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
g. Add commands to your tab and group by locating them on the list on the right hand side (remembering that you can change the list using the drop down box at the top of the list of commands) and clicking the Add button between the two panes to add them to your tab and group
h. You can rearrange the commands in your group, the groups on any tab or the tabs, using the up and down arrow buttons beside the list of tabs.
i. Click OK to apply your changes

View all Excel hints and tips


Server loaded in 0.08 secs.