TEXTJOIN+ IF(ISNUMBER(SEARCH - returning multiple #s

es321

New Member
Joined
Sep 26, 2022
Messages
3
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hi! I have a formula that searches an array named "id" for the ## in cell C2. There are multiple matches and these will be combined in one cell using textjoin and returning the value in the array "place". However, I am running into the issue where it is returning values based on partial numbers found:
for example if the number in C2 is 11215 and then a value it is searching for in the array "id" is 1215, it will return values for both 11215 and 1215 numbers. The equation I am using is this

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(C2,id)),place,""))
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If C2 has 11215 then it should not return 1215.
 
Upvote 0
Hi! I have a formula that searches an array named "id" for the ## in cell C2. There are multiple matches and these will be combined in one cell using textjoin and returning the value in the array "place". However, I am running into the issue where it is returning values based on partial numbers found:
for example if the number in C2 is 11215 and then a value it is searching for in the array "id" is 1215, it will return values for both 11215 and 1215 numbers. The equation I am using is this

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(C2,id)),place,""))
 

Attachments

  • example 2.PNG
    example 2.PNG
    19.4 KB · Views: 20
  • example 3.PNG
    example 3.PNG
    19.7 KB · Views: 20
Upvote 0
In future please use the XL2BB add-in rather than an image. It saves people from having to re-create your data.
Try
Excel Formula:
=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(C2&",",id&",")),place,""))
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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