Matching Number To Number In text

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
71
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
Joined
Jun 8, 2012
Messages
71
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
Joined
Jun 8, 2012
Messages
71
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
Joined
May 26, 2009
Messages
16,691
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
Joined
Jun 8, 2012
Messages
71
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>
 

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
71
BTW I need the 1208015# to populate in the result column I (Joint Use Nbr)
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
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
Joined
Jun 8, 2012
Messages
71
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
Joined
May 26, 2009
Messages
16,691
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
Joined
Jun 8, 2012
Messages
71
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.
 

Forum statistics

Threads
1,085,195
Messages
5,382,248
Members
401,781
Latest member
Dlloyd15

Some videos you may like

This Week's Hot Topics

Top