Matching Number To Number In text

B

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
185
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
[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]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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.


[TABLE="width: 789"]
<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>[TR]
[TD="width: 99, bgcolor: #0B64A0"]Joint Use Nbr[/TD]
[TD="width: 477, 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: transparent"][/TD]
[TD="width: 87, bgcolor: transparent"]TicketNumber[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"]1208015[/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"]27321[/TD]
[/TR]
</tbody>[/TABLE]
 
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
data
data
data
POLE: NJUNS 1208015 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050
more data
more data
more data
more data
more data
more data
more data
POLE: NJUNS 1208000 TRANSFER @ NJUNS REQUEST @ POLE BETWEEN 13082 & 13092 BETHANY RD TERM #13050

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1208015[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]1208000[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

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

[TABLE="width: 789"]
<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>[TR]
[TD="width: 99, bgcolor: #0B64A0"]Joint Use Nbr[/TD]
[TD="width: 477, 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: 87, bgcolor: #4472C4"]TicketNumber[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"] [/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, align: right"]1208015[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
BTW I need the 1208015# to populate in the result column I (Joint Use Nbr)
 
Upvote 0
bwlytkr said:
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.

[TABLE="width: 789"]
<tbody>[TR]
[TD="width: 99, bgcolor: #0B64A0"]Joint Use Nbr[/TD]
[TD="width: 477, 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: 87, bgcolor: #4472C4"]TicketNumber[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"][/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, align: right"]1208015[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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
bwlytkr said:
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?
Click to expand...
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
You must log in or register to reply here.

Similar threads

J
  • Solved
Need to create a formula looking at the 1st 3 digits a 10-digit number in cells (Column G2) that match 3 digits of Column N
Replies
6
Views
973
jfram
J
M
  • Question Question
Formula to match 5-digit numbers in Column A and M, add name/text in Column I to Column B
Replies
8
Views
308
Peter_SSs
Peter_SSs
I
  • Solved
DAX measure that filters a table to a single row and returns text from a specified column (Excel365)
Replies
3
Views
375
Insert_Key
I
A
  • Question Question
Add Sequence numbers Based on Dates
Replies
1
Views
3K
Jeffrey Mahoney
Jeffrey Mahoney
A
  • Question Question
Help with existing code in Post - VBA to Create Org Chart
Replies
1
Views
2K
mrpotato562
M

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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