Search Results For : tutorial

Office Excel – TEXTJOIN Function

Microsoft releases new iterations of Microsoft Office suite every three years for desktop and Microsoft Office 365 is the subscription based cloud version of their Office software. Earlier in 2016, Microsoft released the 2016 edition of Office and some updates to Office 365 which added new features in Excel. Some very useful functions like the CONCAT and TEXTJOIN functions are added which make concatenating or joining text very easier with multiple cells or strings in your spreadsheet. These functions are only available in the latest Office 2016 desktop installation and Office 365 subscription. To show these new functions, here is the Excel CONCAT and TEXTJOIN function tutorial. I am breaking them into 2 different tutorials for ease of access.

The Excel TEXTJOIN function joins or combines text from multiple cells in your spreadsheet with each string separated by a delimiter. The delimiter can be a comma or space. If the delimiter is empty, the Excel TEXTJOIN function will concatenate the strings like in the previous tutorial. Here is how to use Excel TEXTJOIN function tutorial:

The format of the Excel TEXTJOIN function is:
TEXTJOIN(delimiter, ignore_empty, text1, text2, … , textN)

Definition

  • “delimiter” is the character or string inserted between each string you want to join.
  • “ignore_empty” is either TRUE (exclude empty cells or strings) or FALSE (include empty cells or strings).
  • “text1” is the first string or cell and “textN” is the nth string or cell which you want to join.

Now here is how we use the Excel TEXTJOIN function:

  1. In your Excel spreadsheet, see which cells or strings you want to join using TEXTJOIN.
  2. Then select a cell where you want to display the result of the TEXTJOIN function.
  3. For example, in this following example spreadsheet, we want to join the strings in cells A2 through D2 and we want to display the output in cell F2.
  4. Select the cell F2 and enter the Excel TEXTJOIN function in the formula bar above the spreadsheet:
  5. =TEXTJOIN(“ ”,TRUE,A2,B2,C2,D2)

  6. After entering the function, you will see the result in cell F2:
  7. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  8. As you can see in the example, the data in cells A2 through D2 is now joined in cell F2 and there are spaces between the strings since we entered space as a delimiter in the function.
  9. You can do this with numbers as well, like in the result shown in cell F3:
  10. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  11. The result in cell F4 is when TRUE is used:
  12. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  13. The result in cell F5 is when FALSE is used and you can see the difference between step 8 and 9:
  14. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

  15. You can also add strings directly in the function as shown in the following examples:
  16. Office Excel - TEXTJOIN function

    Office Excel – TEXTJOIN function

—–
You can view more Office Excel Tutorials in the link too!

Quick note on Web.config file

A quick note on Web.config file in the ASP.NET project. It is worth noting that the file is a XML File (read this for more info – https://msdn.microsoft.com/en-us/library/ff400235.aspx )

One issue that hindered my process earlier was that the content in the value has special character it in, particularly, one of my SQL server’s password has a special character in it.

I would have to go and replace them accordingly. For instance, my password is <Password1& (note that < and ” is inclusive), i would have to change it to
&lt;Password1&amp;

You can refer to the list here – https://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

Summary of the tutorials posted on my site

Recently I have a few enquries asking me to send them a summary of the tutorials that i have done up and posted on my site.

Just in case any of you are finding that. You can find it here.
Tutorials

or you can click on the “Tutorials” tab on the navigation bar.

BingMapsDirectionsTask – Getting driving / walking directions

I was revisiting my old projects and reflecting on how I could improve on those products. I didn’t realised that there are so much we can do with the new Windows Phone APIs! I was looking at the MSDN’s Microsoft.Phone.Tasks Namespace and found out that there is a Bing Maps Direction Task Class now!

The first thing I thought of was the Green App project which I did during codeXtremeApps 2010 Its a Windows Phone 7 project where we direct our users to the nearest recyling bin around Singapore for them to recyle their waste product. There are more functions which I will not elaborate on here. I will be updating my project/portfolio page soon! I was playing with the Bing Maps Direction Task API just this afternoon. Thought it will be useful to post a tutorial on how to use it. 🙂 Should you face with any problems, feel free to contact me via the Contact Me form or email me at guohong@limguohong.com

Step 1 : Adding using Microsoft.Phone.Tasks and using System.Device.Location into references.

Bing Maps Directions Task 1 - Adding Reference

Bing Maps Directions Task 2 - Adding namespace

Step 2 :  Adding the Microsoft.Phone.Tasks and System.Device.Location into the project

Bing Maps Directions Task 3 - Adding namespace

Step 3 : Initialize a BingMapsDirectionsTask and create a start and end LabeledMapLocation . Note that start is optional. Should you not provide start location, the API will use the current location by default.

At the end of it, .Show() it and it will show the map.

BingMapsDirectionsTask Direction = new BingMapsDirectionsTask();

LabeledMapLocation start = new LabeledMapLocation(“Hougang Ave 4 919 Singapore , Singapore”, null);
LabeledMapLocation end = new LabeledMapLocation(“Paya Lebar Air Base, Singapore”, null);

Direction.Start = start;
Direction.End = end;

Direction.Show();

You can download a usage demostration of the class here. Source code included.

Please note that this is written for WP7.1

Bing Map Direction Task Demo Source Code Screenshot

Bing Map Direction Task Demo Source Code Screenshot

P.S: These 2 classes caught my attention, when I have some spare time, I will look into if I will be able to use it in my Slime Sweeper 2! ShareLinkTask class and Share Status Task Class!

If you have any questions, feel free to contact me via the contact form, comment or email me at guohong@limguohong.com

Do check out the tutorial page too!

Microsoft Excel 2010 – Protecting selected cells

I was at Yishun Town Secondary School the other day to assist on the Gerald, Microsoft School Technology Innovation Center Manager, presentation on a hands on session for Microsoft Office techology.

We were posed some questions, I realised that it is useful to post some of them in a form of guide here so it will help teachers. How do we protect some cells and not allow editing on them while allowing some others to be edited. It will be useful when ICT HODs or teachers want to do up an excel document with the formulas and wish to send to his/her team to use but they do not want them to mess it up.


1. First, lets open up Microsoft Excel 2010.


2. In this example, we will add first and second column and show the result in the third column.
Select A3, go to Formulas tab > AutoSum > Sum


3. Select A1 and A2 for your data range.


4. On the bottom right of the cell, you will see a enlarged black dot, click on it and drag it all the way down to 20th row. ( In this example, we will just make use of 20 rows for the calculation. )


5. You will see something like this.


6. Right click on the 20 cells and go to Format Cells


7. Go to Protection and make sure Locked is ticked. This means these cells are not allowed to be edited.


8. Now Select A1 to B20


9. Right click on the selected cells and go to Format Cells


10. Go to the Protection Tab and untick Locked ( This means these cells can be edited )


11. You might want to color the cells so that your end user will know that these cells can be edited by going to Home tab > Cell Styles > choose Input.


12. Go to Review tab > Protect Sheet


13. Input a password into the dialog box which appears. Please remember the password as you require that to unlock.


14. Another dialog box will appear to confirm the password, input the same password in again.


15. Try inputing values in A1-B20 and you will realised that you can do it but you cant do it when you try on other cells.

This concludes the tutorial on how do you protect selected cells in Microsoft Excel 2010. I have attached the sample document which you can download and have a look.

http://cid-29f099c37b76ca59.office.live.com/self.aspx/Blog/Office%20Demo/Protecting%5E_Selected%5E_Cells.xlsx

Should you have any question, please feel free to contact me at guohong@limguohong.com