Hi!
I am trying to search a cell for a specific item number, in this case KAO-XXXXX where X are digits.
What I want is to check if the cell contains such a number, and if yes, return that number to the adjacent cell.
Something like =IF(K2 contains "KAO-XXXXX"; KAO-XXXXX; " ")
Help?
Last edited by Yngin; 08-04-2011 at 09:42 AM.
Hello,
So, if K2 starts with "KAO-" and ends in a 4 digit number, then return the value of K2?
If so, try
=IF(AND(Len(K2)=8,LEFT(K2,4)="KAO-",ISNUMBER(RIGHT(K2,4)*1)),K2,"")
If that does not do it for you, please post a workbook with some sample data and the expected results. Create the results manually and explain the logic.
cheers,
Last edited by teylyn; 08-04-2011 at 06:32 AM.
Well, not exactly.
The cell contains several lines and sentences of text, separated by blank lines.
The numbers I am looking for are never at the start, and they are "KAO-" followed by 6 digits.
Thanks for helping
If you are checking for specific values as in the example:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"KAO-XXXXX",""))>0,"KAO-XXXXX","")
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Can you post a sample workbook with some manually mocked up expected results?
You say there are 6 digits after KAO, but your narrative shows only 5 X's.
Please post some of your real data to work with to take the guesswork out of the equation.
@TM, as far as I read the question, the XXXXX are meant to be wildcards, not verbatim strings.
Ok, that works for a specific number, great work :D
But how about when the digits behind "KAO-" are unknown? The only certain fact is that there are six digits.
Thanks
You can use the Search() function with wildcards, like this for a string that starts with "KAO-" and has 6 characters after that.
=IF(SEARCH("KAO-??????",K2),MID(K2,SEARCH("KAO-",K2),10),"")
Okay, that kind of worked, only it returned the 10 letters that started 10 digits behind the KAO-number I was after :POriginally Posted by teylyn
Any idea how that happened?
EDIT: Oh, my bad, I had some cell references mixed up. It works now! Thanks a lot, you guys saved me from going through tons of lines manually
Okay, now I just need the lines without KAO-numbers to not return #VALUE!...
That can be solved by a simple IF-sentence, right?
Also, can one make it so that the KAO-number will be displayed only when it is on the form "KAO-XXXXXX" where X is a digit, so that where the cells contains, say, the text "there are different KAO-numbers linked to this", then it won't return "KAO-number"?
If you know what I mean?
Last edited by Yngin; 08-04-2011 at 07:27 AM.
I know what you mean.
stand by
try
copy down
Please Login or Register to view this content.
Sweet, that works perfectly, many thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks