Searching within the result of a vlookup using a range of values and parsing text

1
votes

MY GOAL:

parse a MM/DD date from the result of a vlookup so that it can be used in a project plan

BACKGROUND:

  • The vlookup result contains multiple values separated by a "•" (I don't need all of them)
  • The value I'm looking to parse is not always in the same location in the vlookup result (otherwise I could use the RIGHT formula)
  • There is a finite number of the values I'm looking to retrieve (and I know them already)
  • The value that I'm looking to retrieve contains some text with a date range; I only want the first four values in the date range (MM/DD)
  • I'd like to achieve all this with a single formula with the result in a single cell

CURRENT FORMULA

  • The formula that I've been working on that is not working is:

    =ARRAYFORMULA(if(iserror(search(Iterations!D2:D7,(VLOOKUP(A2,'Results {2596503}'!$C$2:$L$183,3)))),,))

3
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
share a copy of your sheet with example of desired output – player0
here you go: docs.google.com/spreadsheets/d/… – Kyle W
I took a look, but your comments are not clear: "I would like to part "08/17" for the start date but not have col b and just nest the formlus in col c" -and- "I would like to part "08/28" for the end date but not have col b and just nest the formlus in col c." I recommend that you just show the results you want rather than trying to explain them. Also, your sheet is "View Only." Please set the share permission to "Anyone with the link can edit." Otherwise, you will miss out on quality solutions, as many volunteers here bypass sheets that do not offer edit access. – Erik Tyler
In addition, you'll want to give realistic entries for Column B, since parsing out the date portions will require knowing which symbols may ever appear in non-date parts of the Column-B entries (e.g., will there be hyphens elsewhere? forward slashes elsewhere?). – Erik Tyler
Thanks Erik. I updated the results I want in col c and d and also changed the sharing settings. – Kyle W

3 Answers

1
votes

I've set up a sheet called "Erik Help" with the following formulas in B2 ad C2:

=ArrayFormula(IF(A2:A="","",MID(VLOOKUP(A2:A,data!A2:B,2,FALSE),FIND(REGEXEXTRACT(VLOOKUP(A2:A,data!A2:B,2,FALSE),"[0-9]-[0-9]"),VLOOKUP(A2:A,data!A2:B,2,FALSE))-4,5)))

and

=ArrayFormula(IF(A2:A="","",MID(VLOOKUP(A2:A,data!A2:B,2,FALSE),FIND(REGEXEXTRACT(VLOOKUP(A2:A,data!A2:B,2,FALSE),"[0-9]-[0-9]"),VLOOKUP(A2:A,data!A2:B,2,FALSE))+2,5)))

respectively.

They may be longer than actually needed, but you did not share realistic results in Column B or list which symbols may appear in Column B other than in the date; so I tried to account for either a hyphen or a forward slash possibly appearing in Column B in places other than within the date span.

Your analytics sheet also shows a formula that is sorting the results from data!A:A. So even though in your example the original data order happens to be the same as in analytics!A:A, that is not a given (again, based on your formula). Therefore, the VLOOKUP is also necessary.

You did not indicate whether you need to further use these returned date-snippets in calculations, or whether you just need to view them. So the results generated in "Erik Help" are text.

If you want usable numbers/dates, you add further issues that would need to be controlled for in the formula, because you'll only be extracting month and day, not year. That's fine right now. But what about when the date range to be extracted is "12/28-01/13"? If you simply make these values/dates, they will both be assigned to the current year. So the end date here will wind up being earlier than the start date.

Because of this, I've added a second sheet, "Erik Help 2," which contains extended formulas to account for these cases while still returning the date format you want as actual dates which can be used in calculations.

1
votes

EDIT
(following your note on the sheet: "I would like to remove col b altogether and nest in the formulas in col c and d")

You can adjust the range B2:B by replacing it with your already existing formula in B2.

The new adjusted formula will become

=ArrayFormula(IFNA(SPLIT(REGEXEXTRACT(VLOOKUP(ARRAYFORMULA(sort(unique(data!A2:A))),data!$A$1:$C,2),"\d+\/\d+-\d+\/\d+"),"-")))

Original answer

You can use the following formula:

=ArrayFormula(IFNA(SPLIT(REGEXEXTRACT(B2:B,"\d{2}\/\d{2}-\d{2}\/\d{2}"),"-")))

Make sure you format the results as Date.
(Please adjust ranges to your needs)

enter image description here

Functions used:

0
votes

try:

=ARRAYFORMULA(IF(A2:A="",,IFNA(TEXT(SPLIT(REGEXEXTRACT(
 VLOOKUP(data!A2:A, data!A:C, 2), "\d+/\d+-\d+/\d+"), "-"), "mm/dd"))))

enter image description here