ignore/remove leading letter

[secretcodepastedhere]

Hi all!

I have a sheet set up using this formula:

=IFERROR(IF($D7<>0,(INDEX($X:$Y,MATCH($D7,$X:$X,0),2))*1,""),"")

which looks for the id in D in the X column and returns the corresponding # in the Y column. Recently the format for the #'s in Y has been changed to include a day of week identifier (i.e. M605, T605, W605, R605, F605) instead of a zero (which is why it has a *1 at the end currently).

The current formula returns a blank cell with this new format, presumably because it can't multiply a letter. Can someone help me set it to remove the leading letter? I have several other formulae that rely on this output...


Thanks in advance.

 

[Richard Schollar]

Hi

Try:

=IFERROR(IF($D7<>0,MID(INDEX($X:$Y,MATCH($D7,$X:$X,0),2),2,255))*1,""),"")

 

[secretcodepastedhere]

=IFERROR(IF($D7<>0,MID(INDEX($X:$Y,MATCH($D7,$X:$X,0),2),2,255))*1,"")

works ok for anything under 1000, but the ids over 1000 do not have the leading letter and get butchered. I.e. 1003 becomes 3.

 

[Richard Schollar]

works ok for anything under 1000, but the ids over 1000 do not have the leading letter and get butchered. I.e. 1003 becomes 3.

Kinda crucial information there that you didn't include in your first post, huh?

OK try this:

=IF($D7<>0,IF(ISNUMBER(LEFT(INDEX($X:$Y,MATCH($D7,$X:$X,0),2))+0),INDEX($X:$Y,MATCH($D7,$X:$X,0),2)+0,MID(INDEX($X:$Y,MATCH($D7,$X:$X,0),2),2,255))+0),""),"")

 

[secretcodepastedhere]

Sorry about that, I was trying get this in motion right before I left work. I will try this out tomorrow night (3:22am here right now). Thank you for your quick responses!

 

[jasonb75]

Following the lines of your original formula

=IFERROR(IF($D7<>0,IFERROR((INDEX($X:$Y,MATCH($D7,$X:$X,0),2))*1,MID(INDEX($X:$Y,MATCH($D7,$X:$X,0),2),2,255)*1),""),"")

 

[secretcodepastedhere]

Jason, thanks! That one seems to be working! Richard, not quite sure where it's incomplete, but the one you came up with is missing an opening statement somewhere I think since the last ,"),") is showing after the actual end. Not sure if it matters, but I thought I would let you know in case you wanted to look into it for yourself.

Thanks again for the help guys!

 

[jasonb75]

Looking at the way the functions are nested in both solutions, I couldn't help thinking there must be a more efficient formula to do the same thing.

I came up with

=IF(D7<>0,IFERROR(--MID(INDEX($X:$Y,MATCH($D7,$X:$X,0),2),1+COUNTIFS($X:$X,$D7,$Y:$Y,"><"),255),""),"")

which seems to work, but I haven't compared efficiency to the others with large volumes of data.

Any thoughts Richard?