|
Excel
Excel merge fields functions:
CONCATENATE Function - joins 2 or
more strings together.
Concatenate( text1,text2, ... text n) (Up
to 30 strings may be joined)
=Concatenate(A1,B1)
=Concatenate("Tech on the ","Net") would return "Tech on the Net"
=Concatenate(A1,"bet soup") would return "Alphabet soup" (Assuming A1 =
Alpha)
=Concatenate(A1," ",B1) (Creates a space between joined words)
Break Text at Hard Return:
The fast (brute force) way I did
it was with this formula:
=SUBSTITUTE(A1,CHAR(10),";")
Fill down in column B (or wherever), select Column B, copy, paste values
then B1 will contain this:
Ardax;Some Company;123 Any St.;Nowhere, NO
99999
Then you can run "Text to Columns" as above on Column B, using a
semi-colon as your delimter.
As always, there are a multitude of ways in Excel to accomplish things
To separate the name John Smith,
in cell A1, use two formulas:
The formula that extracts the first name is:
=LEFT(A1,FIND(" ",A1))
The formula that extracts the last name is:
=MID(A1,FIND(" ",A1)+1,LEN(A1))
Excel text functions:
LEN Function - returns the length
of the specified string.
Len(text)
=Len(A1)
LEFT Function - extracts a
substring from a string, starting from the left-most character.
Left(text, number of characters)
=Left(A1,3)
RIGHT Function - extracts a
substring from a string starting from the right-most character.
Right(text, number of characters)
=Right(A1,4)
MID Function - extracts a
substring from a string.
Mid( text, start position, number of characters )
=Mid(A1,5,4)
TRIM Function - removes spaces
from text strings.
Trim(text)
=Trim(A1)
Excel change case functions:
First letter of all words uppercase =Proper(A1)
All lowercase =Lower(A1)
All uppercase =Upper(A1)
Excel other functions:
CLEAN function removes all
nonprintable characters from a string.
Clean(text)
=Clean(A1)
EXACT function compares two
strings and returns TRUE if both values are the same, otherwise, it will
return FALSE.
Exact(text1,text2)
=Exact(A1,A2) would return FALSE
REPLACE function replaces a
sequence of characters in a string with another set of characters.
Replace( old text, start, number of chars, new text )
Old text is the original string value.
start is the position in old text to begin replacing characters.
Number of chars is the number of characters to replace in old text.
New text is the replacement set of characters
=Replace(A1,1,5,"Text")
REPT function returns a repeated
text value a specified number of times.
Rept( text,number)
text is the text value to repeat.
number is the number of times to repeat the text value.
=Rept(A1,3)
SUBSTITUTE function replaces a set
of characters with another.
Substitute(text,old text,new text,nth appearance)
text is the original string to use to perform the substitution.
old text is the existing characters to replace.
new text is the new characters to replace old text with.
nth appearance is optional. It is the nth appearance of old text that
you wish to replace. I f this parameter is omitted, then every
occurrence of old text will be replaced with
=Substitute(A1,"oldtext","newtext",1)
TODAY function returns the current
system date.
This function will refresh the date whenever the worksheet recalculates.
Today()
NOW function returns the current
system date and time.
This function will refresh the date/time value whenever the worksheet
recalculates.
Now()
DAYS360 function returns the
number of days between two dates based on a 360-day year.
Days360(start date,end date,method)
start date and end date are the two dates to calculate the difference
between.
method is a boolean value - either TRUE or FALSE. If TRUE is entered,
the Days360 function will use the US method. If FALSE is entered, the
Days360 function will use the European method.
=Days360(A1, A2, TRUE)
Activate multiple hyperlinks
macro Sub editcell()
application.screenupdating = false
For i = 1 To 10
SendKeys "{F2}"
SendKeys "~"
Next i
application.screenupdating = true
End Sub |