Matching Number To Number In text

bwlytkr

Board Regular
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

Board Regular
Sorry Ex. Didn't come out right. I'm trying to match Column Q(TicketNumber) to the # in the description(column J) and show the result in column I (joint use nbr). The number in the description shows in random places in the other cells in column J so not sure if this is doable.


Joint Use NbrDescEntry DtLast Approval DateEncoder NameComplete DtJob TypeTicketNumber
1208015POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050 2/4/2013 SHAW, RITA C. PWO 27321
<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;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="64" style="width: 48pt;" span="5"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <tbody> </tbody>
 

bwlytkr

Board Regular
Any thoughts as to if this is doable or not? I really don't want to have to copy and paste all these numbers over to the result column if I can help it.
 

JoeMo

MrExcel MVP
I don't understand the example you posted in post #2 . The tkt # is unrelated to the Joint use # you show. If the intent is to find which description contains a specific number, maybe something like this:
Sheet2

IJK
141208015data
212
1208000data
3data
4POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050
5more data
6more data
7more data
8more data
9more data
10more data
11more data
12POLE: NJUNS 1208000 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
I1=MATCH("*"&J1&"*",$K$1:$K$12,0)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

bwlytkr

Board Regular
Sorry about that. I'm trying to get the 1208015 # in Column Q (TICKETNUMBER)to find that same # in the cell in Column J (DESC) and pos the result in Column I (Joint Use NBR) Hopefully the Ex. below will clarify.

Joint Use NbrDescEntry DtLast Approval DateEncoder NameComplete DtJob Type TicketNumber
POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050 2/4/2013 SHAW, RITA C. PWO 1208015
<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;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="64" style="width: 48pt;" span="5"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <tbody> </tbody>
 

JoeMo

MrExcel MVP
Sorry about that. I'm trying to get the 1208015 # in Column Q (TICKETNUMBER)to find that same # in the cell in Column J (DESC) and pos the result in Column I (Joint Use NBR) Hopefully the Ex. below will clarify.

Joint Use NbrDescEntry DtLast Approval DateEncoder NameComplete DtJob TypeTicketNumber
POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #130502/4/2013SHAW, RITA C. PWO 1208015

<tbody>
</tbody>
For this example, what is the result you want to see in col I?
 

bwlytkr

Board Regular
Your formula definitely will locate the number in the description field for me. I just need it to populate the number instead of a 1. Any thoughts?
 

JoeMo

MrExcel MVP
Your formula definitely will locate the number in the description field for me. I just need it to populate the number instead of a 1. Any thoughts?
I thought you know the number and want to find the first description that contains the number, no?
 

bwlytkr

Board Regular
Sorry, it looks like my reply didn't post. I need the formula to match any ticket number in column Q to the corresponding number in the description in column j and populate that number in Column I. Sorry I didn't explain it well.
 

Some videos you may like

This Week's Hot Topics

Top