Matching Number To Number In text

[bwlytkr]

Anyone know of a way to match a number in one column to a number inside random text in another column, then populate the match in the formula column. See Below ex.

Column I Column J Column K
Formula column Search Column Column Trying to Match To Text In Column J

Result

Ticket # Description Ticket#


120815
[TABLE="width: 432"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="477" style="width: 358pt; mso-width-source: userset; mso-width-alt: 17444;"> <tbody>[TR]
[TD="width: 99, bgcolor: #F8FBFC"]1208015
[/TD]
[TD="width: 477, bgcolor: white"]POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bwlytkr]

I tried the formula this morning and this is what I got. No errors but the formula continues to show in the cell instead of the calculation. I checked the options tab and interactive calculations is checked. I tried column q as number and general and no change As you can see in the ex. the ticket #'s will not always in line up with the ticket 's in Column J as well.

[TABLE="width: 962"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="720" style="width: 540pt; mso-width-source: userset; mso-width-alt: 26331;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="64" style="width: 48pt;" span="5"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <tbody>[TR]
[TD="width: 99, bgcolor: #0B64A0"]Joint Use Nbr[/TD]
[TD="width: 720, bgcolor: #0B64A0"]Desc[/TD]
[TD="width: 69, bgcolor: #0B64A0"]Entry Dt[/TD]
[TD="width: 64, bgcolor: #0B64A0"]Last Approval Date[/TD]
[TD="width: 64, bgcolor: #0B64A0"]Encoder Name[/TD]
[TD="width: 64, bgcolor: #0B64A0"]Complete Dt[/TD]
[TD="width: 64, bgcolor: #0B64A0"]Job Type[/TD]
[TD="width: 64, bgcolor: #4472C4"] [/TD]
[TD="width: 74, bgcolor: #4472C4"]TicketNumber[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]=IFERROR(IF(SEARCH(Q1,J1)>0,Q1),”")[/TD]
[TD="bgcolor: white"]POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050 [/TD]
[TD="bgcolor: white"]2/4/2013[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]SHAW, RITA C. [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1208015[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: NJUNS 1208022 TRANSFER @ NJUNS REQUEST @ WEBB BRIDGE RD. 5TH POLE EAST OF WESTWIND LN, E. SIDE OF BRIDGE OVER WALKING TRL[/TD]
[TD="bgcolor: #F8FBFC"]2/5/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]SHAW, RITA C. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]804835[/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]POLE: NJUNS 1152355 TRANSFER @ NJUNS REQUEST @ HEMBREE RD O HEMBREE PK TERR [/TD]
[TD="bgcolor: white"]2/5/2013[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]SHAW, RITA C. [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1110109[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: NJUNS 1006608 TRANSFER @ NJUNS REQUEST @ 194 COLD CREEK RD. [/TD]
[TD="bgcolor: #F8FBFC"]2/5/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]SHAW, RITA C. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]33920[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: white"]POLE: NJUNS 1006596 TRANSFER @ NJUNS REQUEST @ S HOUSE 379 MILTON AVE, O NATHAN CIRCLE [/TD]
[TD="bgcolor: white"]2/5/2013[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]SHAW, RITA C. [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1225500[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: NJUNS 1006605 TRANSFER @ NJUNS REQUEST @ O 381 MEADOW DR, TERM #368 [/TD]
[TD="bgcolor: #F8FBFC"]2/5/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]SHAW, RITA C. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]34033[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: white"]POLE: NJUNS 804835 TRANSFER @ NJUNS REQUEST @ 13140 FREEMANVILLE RD. [/TD]
[TD="bgcolor: white"]2/5/2013[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]SHAW, RITA C. [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]34039[/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: NJUNS # 942415 - TRANSFER @ F OF 1307 GREENBRIAR DR - REQ BY [/TD]
[TD="bgcolor: #F8FBFC"]2/13/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]MCDOUGAL, BRENDA M. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1006608[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"]1003905[/TD]
[TD="bgcolor: #F8FBFC"]POLE: NJUNS 1003905 - TRANSFER@ HWY 142 AT NEWBORN CITY LIMITS - REQ BY [/TD]
[TD="bgcolor: #F8FBFC"]2/13/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]TARPLEY, JUANITA W. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]47675[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1110109 [/TD]
[TD="bgcolor: #F8FBFC"]2/13/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]KING, CASSONDRA B. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]48074[/TD]
[/TR]
[TR]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1193589 [/TD]
[TD="bgcolor: white"]2/13/2013[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]KING, CASSONDRA B. [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1193589[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 777180 [/TD]
[TD="bgcolor: #F8FBFC"]2/13/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]KING, CASSONDRA B. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]49014[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: white"]POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1157057 [/TD]
[TD="bgcolor: white"]2/13/2013[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]KING, CASSONDRA B. [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]49180[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1162163 [/TD]
[TD="bgcolor: #F8FBFC"]2/14/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]KING, CASSONDRA B. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]49181[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: white"]POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1225500 [/TD]
[TD="bgcolor: white"]2/14/2013[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]KING, CASSONDRA B. [/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: white"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]49237[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1180539 [/TD]
[TD="bgcolor: #F8FBFC"]2/14/2013[/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]KING, CASSONDRA B. [/TD]
[TD="bgcolor: #F8FBFC"] [/TD]
[TD="bgcolor: #F8FBFC"]PWO [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]49250[/TD]
[/TR]
</tbody>[/TABLE]

 

[Twollaston]

I’m not sure what you’re saying sorry. =IFERROR(IF(SEARCH(Q1,J1)>0,Q1),”")
Where did the  come from? If your pasting this formula into Row 2 then you want to change the cell references to Q2 and J2 and Q2 again. I’m not sure exactly what you mean about the ticket numbers not lining up. In your example you showed the ticket number in the same row. If they don’t line up or show at the correct intervals then the formula won’t be referencing the correct data. What this formula will do is it will search the description for the ticket number and if the ticket number from column Q2 is present in the description in J2 then it will return the ticket number into cell I2, if the ticket number is not present it will return whatever value you have between the quotations(you can put whatever you want it to say)

 

[bwlytkr]

I got it to work and it works great!!! Thanks for all the help. I'll definitely keep this for future use as well.

 

[Twollaston]

I got it to work and it works great!!! Thanks for all the help. I'll definitely keep this for future use as well.

No problem, happy to help you