Column A1:A10 = trade references such as 1234567-10 or 7654321-5 or 1234123/5 , etc etc.
I want to find out whether the trade references (but only the 7 first numbers from left) are in another range on another sheet.
I've tried ISNUMBER + MATCH + LEFT, but I'm having trouble incorporating the limit of first 7 numbers into the function.
Any help is greatly appreciated!
-Cod
Re: Search range for the first 7 numbers of a string.
Hello codtrawler87. Welcome to the forum.
Try array entering something like this.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
=ISNUMBER(FIND("1234567",LEFT(a1:a10,7)))
Dave
You need to show some examples of what the data looks like on the search sheet. eg,
this
or thisHTML Code:
HTML Code:
Last edited by JLGWhiz; 08-08-2019 at 03:47 PM.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
Thanks, I'll try that tomorrow. I have just one concern: if I insert the "1234567" into the formula, won't it just look for 1234567 when I drag it down from A1:A10?
For clarification: I have different trade references in each of the cells in A1:A10 and I want to find out if they are in the other range in sheet2.
Assuming you try to know if the first 7 characters in A1:A10 included in column E (at same worksheet)
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks