Matching Number To Number In text

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
173
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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>
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
BTW I need the 1208015# to populate in the result column I (Joint Use Nbr)
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top