Nested IF ISNUMBER

[Flicka]

Book2 - Google Sheets

Hello, above is a sample of my spreadsheet.

Is there a more eloquent formula to the Nested IF ISNUMBER formula I currently have in Column C?

I need to extract the date from column A.

In future the 'dates list' might be modified to words/phrases/letters, hence why I have used IF ISNUMBER.

Ideally, I need it to go down to row 30 on my 'Lists' sheet.

Thanks

Felicity

 

[FDibbins]

I am not able to view your file, can you explain what you have, and what you want?
And what does your formula look like?

 

[MrTeeny]

If your format is always showing the date as the last 6 numbers why not use something as simple as the following to avoid all the lists etc, just use a simple IF statement if you want to avoid echoing // for empty cells

=LEFT(RIGHT(A1,6),2)&"/"&MID(RIGHT(A1,6),3,2)&"/"&RIGHT(RIGHT(A1,6),2)

If you needed to display it as say 06/01/2017 rather than 06/01/17 you'd just use datevalue and format your cells to display as 06/01/2017

=DATEVALUE(LEFT(RIGHT(A1,6),2)&"/"&MID(RIGHT(A1,6),3,2)&"/"&RIGHT(RIGHT(A1,6),2))

 

[mrhstn]

If they all had a hyphen between the prefix and the date part this would be so much easier. I've not tested it in google sheets, but in excel this might work...

=TEXT(IF(ISNUMBER(FIND("-", A2)), MID(A2, FIND("-", A2)+1, 255), MID(A2, MATCH(2,1/(ABS((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))-77.5))<=12.5))+1, 255)), "00""/""00""/""00")+0

Which would need entered using ctrl+shift+enter. If entered correctly your formula in Excel will be surrounded with braces {=your_formula} in google sheets you'd get =ArrayFormula(your_formula)

 

[thisoldman]

MrTeeny's solution returns the date as text with the form "dd/mm/yy". You might have to swap the "dd" and "mm" portions around.

Like mrTeeny, I'll assume the source text always has the date code as the last six characters. This formula returns the date as an Excel serial date number:

=DATE(2000 + VALUE(RIGHT(A2, 2)), VALUE(LEFT(RIGHT(A2, 4), 2)), VALUE(LEFT(RIGHT(A2, 6), 2)))

 

[MrTeeny]

Yep I stuck it in UK format so you'd just need to swap it to

=DATEVALUE(MID(RIGHT(A1,6),3,2)&"/"&LEFT(RIGHT(A1,6),2)&"/"&RIGHT(RIGHT(A1,6),2))

or just move around to whatever format you needed

 

[Flicka]

Thank you for your help gentlemen. That's really helpful to an extent. I've linked another spreadsheet.

Book3 - Google Sheets

Essentially, I need to extract the locations from the 3 letter location code, and return the full location name.

Basically, instead of =IF(ISNUMBER(SEARCH(Lists!$D$1,A2)),Lists!$C$1,"?")

Something like...

=IF(ISNUMBER(SEARCH(Lists!$D$1:Lists!$D$30,A2)),Lists!$C$1:Lists!$D$30,"?")

I do have Excel 2016, I'm just uploading it to google so you can see what I'm try to achieve.

Thank you once again Sorry to be a pain

 

[thisoldman]

Try this.

Excel 2012
	A	B	C
1	Route		Date
2	EQWLEE-150717		Leeds
3	QSRBAR-150717		Barnet
4	JACBRI-140717		Bristol
5	POILEE-150717		Leeds
6	DHDLAM140717		Lambeth
7	GWCRO-150717		Croydon
List1

Cell Formulas
Range	Formula
C2	=INDEX(Lists!$C$1:$C$16, MATCH(RIGHT(LEFT(A2, LEN(A2) - 6 - ISNUMBER(SEARCH("-", A2))), 3), Lists!$D$1:$D$16, 0))

 

[Peter_SSs]

For the same layout, you could also try this slight variation in C2 of 'List1'

=INDEX(Lists!C$1:C$16,MATCH(LEFT(RIGHT(SUBSTITUTE(A2,"-",""),9),3),Lists!D$1:D$16,0))