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)
LESSON:
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:
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)
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)
LESSON:
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:
=MID(C1,1,7)
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)
results:
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)
Enjoy.
Garmin Nüvi 650, 255WT