Another tip of the day


Here are some useful Excel utilities:

MID: Returns a specific number of characters from a text string, starting at the position you specify.

Syntax: MID(text,start_num,num_chars)

Examples: MID("Fluid Flow", 1, 5) equals "Fluid"

SEARCH: Returns the number of the character at which a specific character or text string is first found, reading from left to right. (Finds one text value within another (not case-sensitive))

Syntax: SEARCH(find_text,within_text,start_num)

Examples: SEARCH("e","Statements",6) equals 7

CHAR: Returns the character specified by the code number.

Syntax: CHAR(number)

Examples: CHAR(65) equals "A"
Examples: CHAR(10) equals line break

CONCATENATE: Joins several text strings into one text string.

Syntax: CONCATENATE (text1,text2,...) MAX=30

Remarks: The "&" operator can be used instead of CONCATENATE to join text items.

Examples: CONCATENATE("Total ", "Value") equals "Total Value".
Examples: "Total"&" "&"Value" (This is the equivalent)

Cell C1 contains:

BigLots,205-425-1499,528 West Town Plaza,Bessemer, AL 35020

to separate the fields into name and address try the following:

Enter into D1:


Result: Biglots

Enter into E1:

=MID(C1,22,SEARCH(",",C1,22)-22)&CHAR(10)&MID(C1,SEARCH(",",C1,22)+ 1,30)&CHAR(10)&"Phone ("&MID(C1,9,3)&") "&MID(C1,13,8)


528 West Town Plaza
Bessemer, AL 35020
Phone (205) 425-1499

Hint: Make sure wrap text is turned on E1
Hint: copy the formala to E2.. to convert the rest
Hint: save the file as CSV and you will lose the
formula but keep the edited text (a warning will be issue)


Garmin Nüvi 650, 255WT