158 useful answers · Page 2 of 7
Use formulas, clean imported data, combine tables, and understand calculation errors.
Search this topicUse =QUERY(A1:D100,"select A, C where D > 100",1). It returns columns A and C where D exceeds 100, treating the first source row as a header. Keep numeric columns consistently numeric because mixed types can affect interpretation. This query language resembles SQL but is Google's visualization query language, with its own supported features.
Use =SPARKLINE(B2:G2) to draw a miniature line chart from those values. Put it in a separate cell and adjust that cell's size so the trend is legible. It is useful for a compact overview, but it does not replace labeled axes and context when someone needs to compare exact values.
Use =IMAGE("image URL") with a directly accessible image address. The default mode fits the image inside the cell while preserving its proportions. A webpage containing a picture is not necessarily a direct image URL, and Google Drive URLs have restrictions. Resize the row and column if the image appears too small.
Use =HYPERLINK(A2,"Open page") when A2 contains the destination address. The cell displays the label while retaining the clickable link. Keep the address in a visible source column if readers need to audit it. A friendly label does not establish that a destination is trustworthy, so verify URLs before sharing a sheet.
Use =REGEXMATCH(A2,"^ORD-") to return TRUE when the text begins with ORD-. The caret anchors the match to the start instead of finding the prefix anywhere in the cell. Regular expressions operate on text and use specific syntax. Test a matching example and a near miss before applying the rule to a whole column.
Use =REGEXEXTRACT(A2,"[0-9]+") to extract the first continuous run of digits. The result is text; wrap it in VALUE if you need a numeric calculation. This pattern does not capture a decimal point or minus sign. Choose a different pattern if your data includes negative amounts or decimal numbers.
Use =REGEXREPLACE(A2,"[^0-9]","") for text data. It replaces each nondigit with empty text. This can clean a phone-number string, but it also removes plus signs, extensions, and formatting, so preserve the original. Keep identifiers as text when leading zeros matter; converting the result to a number may discard them.
Use =SUBSTITUTE(A2,"old","new") to replace occurrences of the specified text. It matches the text exactly, including case, and can change matches embedded inside longer words. Review sample results before filling down. Use the optional occurrence number when only a particular match should change, rather than replacing every occurrence.
Use =REPLACE(A2,3,2,"XX") to replace two characters beginning at the third character with XX. Positions start at one. This is appropriate for consistently structured text, such as a fixed-format code. It is different from searching for a particular word; use SUBSTITUTE when the text's position varies from row to row.
Use =TRIM(A2) in a helper column, then review the cleaned result. It removes leading and trailing ordinary spaces and reduces repeated spaces between words. It does not remove every possible whitespace character, including some imported nonbreaking spaces. Keep the original column until you have checked the cleanup and copied results as values if needed.
Enter =LOWER(A2) in a helper column and fill it down over the required rows. Review the output before replacing the original values. This changes letter case, not spaces or punctuation. For codes, passwords, or other case-sensitive text, confirm that lowercasing is actually appropriate before discarding the original version.
Use =UPPER(A2) in an empty helper cell and fill down for the rest of your records. The formula leaves the original text in place and produces an uppercase result. If you need permanent replacement text, copy the checked results and paste values. Do not overwrite formulas or case-sensitive identifiers without reviewing the effect.
Use =PROPER(A2) to produce an initial-capital version of the text. Review names, abbreviations, and brand spellings afterward because a mechanical case conversion does not understand preferred capitalization. For example, an all-capitals abbreviation may lose that styling. Keep the original alongside the result until you have corrected any exceptions.
Use =LEFT(A2,3) to return the first three characters from A2. The result is text, which is helpful for prefixes and codes. Check that all records really use the same prefix length before filling down. If the boundary is a delimiter such as a hyphen, splitting or searching for that delimiter may be more reliable.
Use =RIGHT(A2,4) to take the final four characters of the cell's text. Trailing spaces count, so inspect imported records if the result looks shifted. This does not validate the meaning of the extracted suffix. Preserve the source if you are using the output to match accounts, products, or another identifier.
Use =MID(A2,4,3) to return three characters starting at position four. Character positions begin at one. This works well when every code has a consistent structure. If some records have longer prefixes or optional fields, identify a delimiter or pattern instead of assuming the same character positions will work for every row.
Use =LEN(A2) to count the text's characters, including spaces. It can help check length limits or spot unexpected extra spaces in imported values. A displayed number may not reveal all underlying text details, so inspect the source when a count surprises you. This is a character count, not a word count.
Use =FIND("ID",A2) to return the starting position of the first exact-case match. Positions start at one, and a missing match produces an error. Use IFERROR only if you deliberately want to handle that absence. For a search that should ignore letter case, use SEARCH instead of silently changing the input text.
Use =SEARCH("blue",A2) to get the position of the first case-insensitive match. For a simple TRUE/FALSE check, wrap the result in ISNUMBER. Remember that finding a substring does not prove it is a whole word. Test representative data if your keyword could also appear inside a longer word.
Use =VALUE(A2) when the text is in a number, date, or time format Sheets recognizes. Check the spreadsheet's locale if decimal or thousands separators are interpreted incorrectly. Keep identifiers such as ZIP codes as text when leading zeros matter. Review converted examples before replacing the original imported column.
Use =TEXT(A2,"00000") to create a five-character text representation of a suitable number, such as 00042. The result is text rather than a number with a display format. If the original code was already longer or contained letters, do not force it through this numeric pattern. Choose the method according to the code's structure.
Use =EXACT(A2,B2). It returns TRUE only when the text matches exactly, including letter case. Extra spaces can also make visually similar values different. This is useful for checking text imports, but do not automatically trim or change case if those differences are meaningful. Investigate mismatches before declaring a record incorrect.
Use =REPT("*",B2) to produce the number of asterisks specified in B2. Make sure the count is a sensible nonnegative value and keep very large outputs out of ordinary sheets. This can create a simple visual marker, but a repeated-symbol bar needs a clear scale if readers are comparing quantities.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.