substring

  1. T

    Formula to extract substring between two characters

    I have a list of path names in column A and would like to extract the file names which come after the last "/" and before the last "." to be placed in column B. I also want all "%20" to be replaced with a space. For example, cell A1 contains...
  2. E

    Substring Search !

    Hello! So let's say I have: Dim MainString as string, Substring as string Dim Result as Boolean MainString ="ABCDE|ABCDF|CGHMN|QRSTU|CJKLM" Question: Is there an easier (faster) way to check whether MainString contains all elements of Substring without using loops? Example 1: If Substring...
  3. S

    VBA - Using InStr to find substring and cut characters into offset cell

    Can anyone help with an error I am getting when using the InStr function please? :confused: I want the code to identify the column with header 'To' which can occur in variable position, then search the 'To' column for a specific substring. For each cell containing the substring I need the...
  4. S

    VBA - Using InStr to find substring and cut characters into offset cell

    Can anyone help with an error I am getting when using the InStr function please? :confused: I want the code to identify the column with header 'To' which can occur in variable position, then search the 'To' column for a specific substring. For each cell containing the substring I need the...
  5. S

    Find numbers before unit of measurement in string

    Hi, I've got what seems like a simple conversion task that I'm having trouble puzzling out. I have a column of product descriptions (text/html) that contain measurements (i.e. "Medium - 6.3cm x 7cm") and I need to find and convert them from cm to inches. I'm having trouble figuring out how to...
  6. S

    Find best partial match(es) for substring(s)

    This might be an impossible task, but I'm in a de-duping project and was hoping to write a formula that could plug in a string from before the @ in an email address, say "harrystruman" and then compare that to a range of values in a column or columns, and find the best match or partial match (or...
  7. S

    VBA, Sum part of substring

    Hi, Setup: I have data in colum "B:D". In column "B" there is an item number, in column "C" there is a category of the item (always 2 digits long) and in colum "D" I have an amount for each category. Note that all values in column "C" and column "D" is in the same cell (ctrl+alt+enter). Note...
  8. S

    Find and remove list of substrings from larger list of strings

    I have three lists: > LIST 1 is a set of substrings that need to be removed from the main list - LIST 2. The substrings are either a prefix or a suffix (either the beginning or end of one of the strings in LIST 2). > LIST 2, as mentioned, is the main list that needs to have one of the...
  9. K

    INDEX MATCH Multiple Substrings

    {=IFNA(INDEX(NamedRange,MATCH(TRUE,ISNUMBER(SEARCH(NamedRange2,CellcontaingStringoftext)),0))’””)} This array is looking a up list of words that could be included anywhere in the string of text and returns the corresponding value of next column of the first identified substring, if it does not...
  10. J

    Replacing Multiple Values with Different Values

    I'm working on a project for work where I need to turn SKU numbers (just a string of numbers and characters) into a regular word. For example, if the cell contains "sahara" (full cell is jmackey-sahara-XL), I want to have a corresponding adjacent column that says "Sahara." There isn't a...
  11. A

    if cell contains substring remove the substring

    folks, I need to remove the title "Dr" from a list of names prior to publishing. Not all names have this title. I can find the substring within my specific range with pos = InStr(nm1, "Dr ") where nm1 contains a name. nm1 refers to the cell C9 on my spreadsheet. if nm1 does not contain the...
  12. C

    Using match, index to return substring

    My spreadsheet consists of: column A: single numbers or letters from 1-10 and a-j column B: first-name sp last-name. (Abe Lincoln, George Washington, etc) column f: single numbers or letters column g: return last name only. how can I combine both following formulas to yield the last name only...
  13. A

    VBA: Replace substring in a cell

    Hi In A1 to A5, there is a cell with a substring that I want to replace. I tried the below code but it doesn't work Note that in A1 to A5, there are some empty cells or with text. Thank you. Sub test () Dim p as range For Each p In Activesheet.Range("A1:A5") p.Value =...
  14. T

    Substring search in a lookup on a Pivot

    Hi, Currently have this formula that works to a point: =IFERROR(GETPIVOTDATA("Var1",Data!$K$1,"Var2",$AD18222,"Var3",I$2,"Var4",$AB18222),0) I need to be able to substring Var4 so it finds for example "VF11" VF12" etc when the cell it refers to ($AB18222 for example) has "VF1" in it. Anyone...
  15. A

    Get Substring from a cell with variable length string

    Hello Excelperts, I find myself in a bit of a conundrum and looking for your help and expertise to learn how to pull a substring from a cell of variable length string. From the transcript column A1, I have variable length transcript string, what I am looking to extract is the name of the...
  16. K

    VBA Hide Pivot entry featuring text substring as part of string

    Please can anyone help? I would like to hide PIVOT row entries featuring a specific substring as part of the text string for that entry. By using the following code, I can specify the name of a full entry to be hidden: With ActiveSheet.PivotTables("PivotTable1").PivotFields("SPECIFIED FIELD")...
  17. R

    SEARCH function only finds full string, but not substring

    Hi all, Background: I was searching for a formula (for conditional formatting) that checks if a specific cell (text format) contains a specific text of an adjunctive cell as a substring. If there is no match, the cells should be highlighted red. As an example: <tbody> 01 ABC_01_XXX 22...
  18. C

    Search Substring for data that shouldn't be on Spreadsheet

    Hi, I am trying to come up with an easy way in Excel 2010 to search column F for data that should not be there. Column F contains a string of data which contains a number. This number refers to the Job that has been undertaken, for Example A3 - 1G 123456 Data String. I know that the number...
  19. T

    VBA to extract multiple substrings into another worksheet

    I have around 180 records that have an 80 character string where one row (H1 *) contains an account number the next row (R12*) that contains detail that needs to be separated into different columns on another worksheet. I can use a MID function to extract but I think it would be more efficient...
  20. E

    Excel 2013 Calculation Based on Substring Content

    I have a spreadsheet in which column D always contains substrings of "women's", "women" or "womens" for women's clothing, or "men's", "men" or "mens" for men's clothing. Based on whether the substring is women's or men's, users enter a whole number value in column I, <= 20000 for women's and <=...

Some videos you may like

This Week's Hot Topics

Top