How to reconstruct a bates number

[Delta729]

I'm working on a template that will allow me to reconstruct a bates number from opposing counsel and I can't figure out how to go about it. We sometimes get a bates number range such as "Bates00000123-32". That means this is a range of numbers from 132 thur 132. I can extract the text and numbers from each other, but I can't figure where to go from there. Because the second part of the bates number is basically abbreviated, and drops the number that is the same (meaning a "1" in this case). I'm trying to devise a way to make the "32" portion actually look like "132". It could also be something like "Bates00001085-154". So, 1085 for the first part of the bates and 1154 for the second portion.

For example sake, say cell A1 has 123 and cell A2 has 32. It's like I need add what numerical spot or spots are missing and add that back on to the second portion.

I hope that makes sense and help would be appreciated.

[oeldere]

c1 =A1&"-1"&B1

[Delta729]

Well, I guess that work in certain situations, but there is often a lot larger bates range. I use this formula when I build a bates range in a cell.

=IFERROR([@[Full First Bates]]&"-"&RIGHT([@[Full Second Bates]],LEN([@[Full Second Bates]])+1-MATCH(FALSE,INDEX(MID([@[Full First Bates]],ROW(INDIRECT("1:"&LEN([@[Full First Bates]]))),1)=MID([@[Full Second Bates]],ROW(INDIRECT("1:"&LEN([@[Full Second Bates]]))),1),0),0)),""&[@[Full First Bates]])

I just can't figure out how to go the basically go the other way around.

[oeldere]

add an small excel file without confidential information on this forum.

please also add the result manualy in the file.

[sandy666]

Use fx for formula not a QUOTE, like this:

Please Login or Register  to view this content.

[Delta729]

I've attached a small file with two column shaded in yellow. Column E is what it should say in column D. I hope I gave enough examples there in Column E.

[oeldere]

I used a VLookup table to find the data.


g4=Right(A4,LEN(A4)-5)*1

i4=if(H4="","",if(LEN(G4)=LEN(H4),max(G4,H4);MAX(G3,VLookup(LEN(G4),$K$1:$L$3,2,0)+H4)))

See the attached file.

[Delta729]

Okay, that was my fault, I clearly didn't give enough examples about the possibilities. So I revised the test file from what was returned yesterday and changed few examples. While I get the concept of where this could go, there so many other possibilities. For example, instead of just a number that was in the hundreds, what about the potential of the bates numbering being in the multiple thousands, and the second bates number only being in the hundreds, or even less. Also, even one of the examples doesn't work right on row 8.

I really appreciate the effort, but I think this is wrong way to go about it. Any other suggestions.

[oeldere]

Bates00805206 6425 805206 #N/B 1125

WHY is the result of this row 1125 => please explain.

[Delta729]

I don't understand this result at all, but it's in the spreadsheet. I reattached the sample again, but this time with an additional small table that shows what I use to make the combined numbers, down on cell C17. Maybe I should NOT be using the "text to column" feature to split the number up into different cells based on the "-" dash. And instead be using the combined numbers like in cells C17 thru C19 to calculate what the second portion (after the dash) should be? Does that make any sense.

[Richard Buttrey]

Hi,

Apart from the seeming anomaly that oeldere has flagged up perhaps I4 copied down.

Please Login or Register  to view this content.

[Delta729]

Okay, I'm pretty sure I have it working. I added two additional columns and used a LEN function in both. Then I added this formula in the cell where I wanted the full 2nd bates number. I seems to be working, but you never know. I posted it in case anybody else has any other ideas and so the can see what I was trying to do.

IF([@[2nd Bates LEN]]<[@[1st Bates LEN]], LEFT([@[1st Bates extracted]], ([@[1st Bates LEN]]-[@[2nd Bates LEN]]))&[@[2nd Bates]], IF([@[2nd Bates LEN]]=[@[1st Bates LEN]], [@[2nd Bates]], IF([@[1st Bates LEN]]<[@[2nd Bates LEN]],[@[2nd Bates]],"")))

Thanks for you help.

[Richard Buttrey]

..or a variation to my earlier formula since the column refs are different, in F4 copied down (no helper columns required)

Please Login or Register  to view this content.