Type conversion

In Excel, the CStr function converts a value to a string.

The CStr function can only be used in VBA code. For example:

Dim LValue As String

LValue = CStr(8)

The LValue variable would now contain the string value of "8".

Convert dates stored as text to dates :  =DATEVALUE(cell that contains the text-formatted date that you want to convert)

Convert a number saved as text to a number:

=IF(RIGHT(A2;1)="-";-1*SUBSTITUTE(SUBSTITUTE(LEFT(A2;LEN(A2)-1);".";"");",";".");1*SUBSTITUTE(SUBSTITUTE(A2;".";"");",";"."))

Concatenation

Combine text and numbers from different cells into the same cell by using a formula

To do this task, use the CONCATENATION and TEXT functions and the ampersand (&) operator.

 

1 2 3

A B Salesperson Sales Buchanan 28 Dodsworth   40%   Formula Description (Result) =A2&" sold "&B2&" units." Combines contents above into a phrase (Buchanan sold 28 units) =A3&" sold "&TEXT(B3,"0%")&" of the total sales." Combines contents above into a phrase (Dodsworth sold 40% of the total sales) =CONCATENATE(A2," sold ",B2," units.") Combines contents above into a phrase (Buchanan sold 28 units)

Note the use of the TEXT function in the formula. When you join a number to a string of text by using the concatenation operator, use the TEXT function to format the number. The formula uses the underlying value from the referenced cell (.4 in this example) — not the formatted value you see in the cell (40%). The TEXT function restores the number formatting.

Tags : Excel