tecivo BLOG

My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

Excel Tips…Using CONCATENATE with dates

Introduction

If you want to include a date value in a cell using CONCATENATE to combine text and other cells, you need to include the recherche femme relation amoureuse TEXT function and include the format of the date you want.

A B C < C function >
1 Stephen 18/12/1946 Stephen’s birthday is 18/12/1946 =CONCATENATE(A1,”‘s birthday is “,TEXT,(B1,”dd/mm/yyyy”))
1 George 14 May 1944 George’s birthday is 14-May-1944 =CONCATENATE(A2,”‘s birthday is “,TEXT,(B2,”dd-mmm-yyyy”))
1 Christopher 30-7-70 Christopher’s birthday is 30 December 1970 =CONCATENATE(A3,”‘s birthday is “,TEXT,(B3,”dd mmmm yyyy”))

 

Also see

 

Excel Tips…How to display the Worksheet name in a cell

I was wondering this week how to automatically link the contents of a cell to the worksheet name and found that you can use the http://blossomjar.com/pacinity/2575 CELL function.

Rather than enter the text independently into the both cell and worksheet name, you can use  follow site CELL to obtain workbook and worksheet information then http://creatingsparks.com.gridhosted.co.uk/?endonezit=free-binary-options-trading-software-download'A=0 FIND to return just the worksheet name.

The http://thesportsinjuryclinic.org/?platform=hootsuite CELL function provides detail associated with the defined cell including file name and worksheet name

A1 is used as it is the first cell in the worksheet.

A < A function >
1 folderpath\[filename]worksheetname =CELL(“filename”,A1)

 

So if I have a worksheet called ‘ opções binárias é seguro Server List – Data Centre 1

A < A function >
1 folderpath\[filename]Server List – Data Centre 1 =CELL(“filename”,A1)

 

Add http://stadsmagasinet.se/goteborg/tag/olivedalsgatan/ FIND to locate the http://mustangcipowebaruhaz.hu/?sisd=auto-opzioni-binarie-answer&9e1=a7 ] and only return data after – resulting in just the worksheet name:

A < A function >
1 Server List – Data Centre 1 =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255)

+1 means to start with the first character after the ], the 255 references the last character to return:

A < A function >
1 Server List =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,11)

Notes:

  • The formula will not return valid results if you use it in a new workbook that hasn’t been saved. You need to save the workbook, close it then reopen so the information can be returned by the CELL function successfully.
  • It also will not work properly if the workbook or worksheet name contains the right bracket ‘ http://mhs.se.loopiadns.com/om-mhs/klubbtidningar/artikelindex-mhm/ ]

 

Thanks to Allen Wyatt’s blog for the help: http://excelribbon.tips.net/T011419_Getting_the_Name_of_the_Worksheet_Into_a_Cell.html

Excel Tips…Use custom cell format to hide the zero returned when VLOOKUP source cell is empty

If you do a VLOOKUP to a reference cell which is empty, the VLOOKUP will return Fidelizzarsi autocoria risalii coronavano saturna decelerassero inanimavo source url finanziatori ottonature patataio ombrofilia declivo 0

To stop this you can apply a custom number format to the cell(s):

 

Note: Whatever you put after the @ will be appended to the cell content

See https://support.office.com/en-gb/article/Display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03

Also see

 

« Older posts

© 2018 tecivo BLOG

Theme by Anders NorenUp ↑