Excel functions: 7 ways to use Text functions to manage data - watsonhicamen

Excel's Text functions are a better time saver if your Job entails managing massive data, especially data that's imported from other sources. Fortunately, all ASCII data is easily imported, but the format of that imported information can vary drastically from one source to another.

For example, William Claude Dukenfield Crataegus laevigata be delimited, such as with tabs, spaces, commas, or periods. Aany strange characters are all preferable to spaces. That's because spaces non only exist in between fields, they also separate words within the fields, which makes sorting out the fields a material challenge. That's where the followers Text functions are very facilitative.

A. Use the SUBSTITUTE function to supervene upon one string of text edition with another

Function syntax: The phrase structure (or phrase structure) of the Step in function is this:

=Stand in(text edition, longtime text edition, new text, [Instance Num]).

Note: If you don't particularise an Instance Num, every occurrence of the Elderly Schoolbook is changed to the New Text. If you specify the Example Num, only that occurrence of the Old Text is replaced. E.g., ingress the number '1' means you want to change only the outset occurrence of that word in the string.

1. Enter whatever phrases in pillar A (from A2 through A13).

2. Enter the word of honor Oregon phrasal idiom you want changed in newspaper column B.

3. Enter the word or phrase you want to supersede the gaga text with in column C.

4. Enter the following recipe in cells D2 through D7 (or half the database): =SUBSTITUTE(A2, B2,C2,1)

This changes/replaces only the first occurrence of the Old Text to New Text.

5. Next, enter this formula in the remaining cells (in our pillow slip, D8 through D13): =SUBSTITUTE(A2, B2,C2). This changes/replaces all occurrences of the Former School tex to New Textual matter.

Note: This function is case-nociceptive, so if your results aren't working, change the text to all the same case.

PC Globe / JD Sartain

01 Use the SUBSTITUTE function to supercede one string along of text with another

For this instance, the physical object is to extract the last word—that is, the last mention, from a chain of schoolbook (the full names of a list of clients).

1. Insert some names in column A: first, survive, and middle names or initials.

2. Insert this formula in B2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)).

3. Copy the formula from B2, shoot down to B3 direct B1000 (or the end of your database). For this example, we're presumptuous your database has 1,000 records.

4. This formula works because the Backup man function locates all the spaces in the string of text, and then replaces from each one single space with 50 spaces. The RIGHT function removes 50 characters (from right to left), and the TRIM function deletes all the surplus leading spaces leaving just the single, last word. If you have longer string section of text, hear substituting 100 operating theatre more for the 50 values in the higher up normal.

Microcomputer World / JD Sartain

Extract the last word in a string of textbook

This formula works when you need to separate the first name from the middle and family name of a heel of clients.

1. Enter some name calling in column A (or use the same names from the early exercise).

2. Enter this chemical formula in B2 through B1000: =LEFT(A2,SEARCH(" ",A2)-1) to extract the first name of each client into a separate column.

PC Universe / JD Sartain

Express the first word/name in a string of text

D. Extract everything except the first password in a string of school tex victimisation TRIM, RIGHT, REPT, & SUBSTITUTE

The purpose of this use is to remove the honorifics from a list of client names. These clients are providing confidential survey information, so the troupe does not want the titles and salutations of each individual to influence the surveyors.

1. Go in some more names in column A (Beaver State use the same name calling from the previous exercise). Enter some honorifics before each call; e.g., Mr., Miss, Ms, Mrs.. , Dr., Sir, Lord, Madam, Capt., etc.

2. Enter this formula in B2 through with B1000: =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",60)),180)) to excerpt the full name calling of all the clients disadvantageous the honorifics.

3. And, if you wanted to excerption the honorifics (for some reason), enter this expression in C2 through C1000: =Liberal(A2,SEARCH(" ",A2)-1).

PC Humans / JD Sartain

Extract everything EXCEPT the first word in a string of school tex

The bottom job of the day is to spend hours manually retyping client names or domain names from e-mail addresses, especially when the list is over 5,000 names. Use the following formulas to complete this labor in proceedings.

1. Enter some email addresses in column A.

2. Enter this formula in B2 through B5000: =LEFT(A2,FIND("@",a2)-1) to extract the full name calling of every last the clients.

3. Enter this chemical formula in C2 through C5000 to remove the underscore between the first and last name: =SUBSTITUTE(B2," ","_").

4. Move to cell F2. Select Formulas >School tex > . Typewrite C2 in the Text discipline box on the Functions Arguments dialog screen, or click prison cell C2, and so click OK. This chemical formula converts the names to Proper Case (that is, first letter of the kickoff and cognomen capitalized, altogether other letters in lowercase).

5. Copy the formula in F2 to F3 through and through F5000 and press Enter.

PC World / JD Sartain

Extract names from email addresses

G. Extract domains from electronic mail addresses using TRIM, LEFT, SUBSTITUTE, MID, FIND, LEN, &A; REPT

1. Enter this formula in D2 through D5000 to extract the world name calling from the email addresses:

=TRIM(Left field(Second-string(MID(A2,FIND("@",A2),LEN(A2))," ",REPT(" ",100)),100))

2. And parting, enter this formula in E2 through with E5000 to remove the @ signs from the extracted domain names: =SUBSTITUTE(D2,"@","").

PC World / JD Sartain

Extract domains from email addresses

Source: https://www.pcworld.com/article/406027/excel-functions-7-ways-to-use-text-functions.html

Posted by: watsonhicamen.blogspot.com

Share this post