How to change relative hyperlink to exact link in Excel

You may have used the HYPERLINK function in Excel before but what you may not have released is your links can be either relative or absolute (or sometimes called exact or specific). What does this even mean? And how do you change from relative to exact?

How to Combine text from two or more cells in Excel

I'm trying out a new idea I have for sharing Excel tips: creating a short but very targeted PowerPoint presentation on Slide Share about how to do a specific task in Excel. Please let me know if you like this format or not.

Did you know that you can combine or merge text from two or more cells into one cell in a spreadsheet? There are, of course, multiple ways to accomplish this task. One way to do this is to write a formula in a third column which uses the concatenation operator, the ampersand (&).

The CONCATENATE function can also be used to join different pieces of text together. However, in Excel 2016 CONCAT replaces the CONCATENATE function. The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. Also new in Excel 2016 is the TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined.

How to Combine text from two or more cells in Excel from Nick Weisenberger

Please let me know in the comments below if you want to see more short presentations like this one! I also posted this as a short video on YouTube and on Facebook, would you prefer this as a video instead?

How To Get The Best Out of The Concatenate Function

The following is a guest post from Puneet at Excel Champs:

Text functions averagely cover  10%-15% of our Excel usage on a day to day basis. Text functions help us to present a numeric value in a simple way. If you check the below example, a single sentence is describing the performance for the September month. But we can't say there is something wrong in presenting results in tabular form. It is just a matter of requirement. Excel has lots of useful functions in this Text Function family (Upper Function, Lower Function, Proper Function etc....).

CONCATENATE Function

A function to combine text, a function to put the text together. Yes, this is the CONCATENATE Function. A Function which exactly do the same for text as SUM Function does with numbers (Giving results in a single number).

How Does It Work?

It takes the text one by one in arguments & convert them into a single string.

Syntax: CONCATENATE(text1, text2, ...text255) 

text1 is the first item to join in concatenation which is also a required argument. 

text2 ... is the additional text which you can add. You can add up to 255 items & up to 8192 characters in a single function(for Excel 2007 & Later). Each entry must be separated by a comma.

If we concatenate two strings like "Hello" & "World", then it would be like =CONCATENATE("Hello ","World"). Now if you notice we had a space between the two strings, which is quite useful while converting them into a single string. The above example is drawn by using absolute input for arguments, you can also use cell reference to add arguments.  =CONCATENATE(A1," ",B1).

How Can It Be Useful?

It can help you at a number of times.

• Writing a conclusion for a tabular data, which will update with the changes in your data. So that, you don't have to write it again & again.
• Converting two or more strings into a single string. So that it can make a meaning to you.

Example

Here we have an example where we are capturing the population of the major cities in the world in a string and function will be.

HURDLES

Yes, we have. But you don't have to worry.

• Using commas to differentiate one string from another is confusing sometimes. If you skip putting a comma between the arguments, it can lead to a different result. =CONCATENATE("Excel""Champs") results in Excel"Champs. You can also put ampersand(&) instead of Comma(,).
• The space between string is also a point to take care. You can enter space within the string "Hello " or by inserting it separately "Hello"," ".
• If you use concatenation to join a numeric value, that value will always show in general format. That means if you have a number with 10 decimals, you will get it in the same decimal format in your string(Total growth in this month is 0.0523652). But this problem is not long lasting, you can kill it with TEXT Function & make your number just like you want.

What else to do?

Sometimes using a function like CONCATENATE is time-consuming, if you have to put it one by one different string to create a new one. You can also use a shorthand trick to get results like CONCATENATE, just ampersand(&) to join the text as do in CONCATENATE. It will give you a simple & a fast hand to join text.

Download a sample file here to see examples:

Sample File

Thanks to Puneet for providing us with this explanation of concatenate. 

How do I combine two cells in Excel?

Sometimes you may want to combine into one column text that is currently contained in two columns. One way to do this is to write a formula in a third column which uses the concatenation operator, the ampersand (&). This formula in cell C1 would return the contents of A1 followed by the contents of B1:

=A1&B1

If you want to include a literal character, say a space, use the form:

=A1&" "&B1