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

1208015

POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050

<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> </tbody>

 

[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.

Joint Use Nbr	Desc	Entry Dt	Last Approval Date	Encoder Name	Complete Dt	Job Type		TicketNumber
=IFERROR(IF(SEARCH(Q1,J1)>0,Q1),”")	POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050	2/4/2013		SHAW, RITA C.		PWO		1208015
	POLE: NJUNS 1208022 TRANSFER @ NJUNS REQUEST @ WEBB BRIDGE RD. 5TH POLE EAST OF WESTWIND LN, E. SIDE OF BRIDGE OVER WALKING TRL	2/5/2013		SHAW, RITA C.		PWO		804835
	POLE: NJUNS 1152355 TRANSFER @ NJUNS REQUEST @ HEMBREE RD O HEMBREE PK TERR	2/5/2013		SHAW, RITA C.		PWO		1110109
	POLE: NJUNS 1006608 TRANSFER @ NJUNS REQUEST @ 194 COLD CREEK RD.	2/5/2013		SHAW, RITA C.		PWO		33920
	POLE: NJUNS 1006596 TRANSFER @ NJUNS REQUEST @ S HOUSE 379 MILTON AVE, O NATHAN CIRCLE	2/5/2013		SHAW, RITA C.		PWO		1225500
	POLE: NJUNS 1006605 TRANSFER @ NJUNS REQUEST @ O 381 MEADOW DR, TERM #368	2/5/2013		SHAW, RITA C.		PWO		34033
	POLE: NJUNS 804835 TRANSFER @ NJUNS REQUEST @ 13140 FREEMANVILLE RD.	2/5/2013		SHAW, RITA C.		PWO		34039
	POLE: NJUNS # 942415 - TRANSFER @ F OF 1307 GREENBRIAR DR - REQ BY	2/13/2013		MCDOUGAL, BRENDA M.		PWO		1006608
1003905	POLE: NJUNS 1003905 - TRANSFER@ HWY 142 AT NEWBORN CITY LIMITS - REQ BY	2/13/2013		TARPLEY, JUANITA W.		PWO		47675
	POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1110109	2/13/2013		KING, CASSONDRA B.		PWO		48074
	POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1193589	2/13/2013		KING, CASSONDRA B.		PWO		1193589
	POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 777180	2/13/2013		KING, CASSONDRA B.		PWO		49014
	POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1157057	2/13/2013		KING, CASSONDRA B.		PWO		49180
	POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1162163	2/14/2013		KING, CASSONDRA B.		PWO		49181
	POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1225500	2/14/2013		KING, CASSONDRA B.		PWO		49237
	POLE: TRANSFER @ NJUNS REQUEST / NJUNS # 1180539	2/14/2013		KING, CASSONDRA B.		PWO		49250

<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> </tbody>

 

[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