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 this formula but, it didn't work. Column has the identical Ticket Numbers as column Q

=INDEX(R:R,MATCH("*"&J2&"*",Q:Q,0))

 

[JoeMo]

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.

Doesn't that just return the number in Q or an error if there's no match? Wouldn't you want to know the location of the description in col J that matches the number in Q?

 

[JoeMo]

I tried this formula but, it didn't work. Column has the identical Ticket Numbers as column Q

=INDEX(R:R,MATCH("*"&J2&"*",Q:Q,0))

If the description is in col J then that formula trys to return the value in col R for which there is a match in col Q to the entire description in col J. That's not going to work.

 

[bwlytkr]

That's right it doesn't work. I'l try to explain different way. If the number in the description matches a ticket number in column Q, I need the matched ticket # to appear in column I where the formula is.

 

[JoeMo]

That's right it doesn't work. I'l try to explain different way. If the number in the description matches a ticket number in column Q, I need the matched ticket # to appear in column I where the formula is.

Can you provide some more example data (with col letters above the headers) so we can see the range of descriptions and look for consistent placement and length of the ticket numbers within the description? For example, is the first digit of the ticket number in the description always the 13th character in the description? is the ticket number always followed by " TRANSFER"? What do you want the formula in col I to return if the ticket number in the description can't be found in col Q?

 

[bwlytkr]

I don't have any further examples tonight as the spreadsheet is a t work. However, the ticket # in the desc column is not always in the same place in the string of text. It appears random in each cell which is my problem. I'll send more examples tomorrow if you still need them. I appreciate you sticking with me oaths and trying helping me out

 

[bwlytkr]

The ticket # doesn't always follow TRANSFER and if the ticket # can't be found then N/A is fine or whatever it defaults to.

 

[JoeMo]

The ticket # doesn't always follow TRANSFER and if the ticket # can't be found then N/A is fine or whatever it defaults to.

Send some more data which illustrates the breadth of ways in which the ticket number appears in the description. If it's random that may make a formula solution problematic. Are you willing to use VBA in that case?

 

[bwlytkr]

Will do. Never used VBA before but, will to try it.

 

[Twollaston]

That's right it doesn't work. I'l try to explain different way. If the number in the description matches a ticket number in column Q, I need the matched ticket # to appear in column I where the formula is.

=IFERROR(IF(SEARCH(Q1,J1)>0,Q1),"")
Paste this into column I