Compare first 4 characters and if matched extract information from cell
Hi,
I have two columns:
- inventory number: cell with numbers and lettes: 1, 2, 8B, 81D, 160, 409B etc
- picture's filename
I want to compare the first 4 characters in column A with column B and if they exactly match then extract the cell in column A.
I built a formula in C2 with what i know.
I found some posts with ISNA an MATCH, tried them but is the first time i use them.
I attached an example.
Please guide me,
Thank you
This doesn't make sense to me. You say you want to compare the first four characters in column A exactly to column B, but there are only six (6) records in column B that have four characters. More information is needed to complete this accurately.
Spread the love, add to the Rep
"None of us are as smart as all of us."
is this it? In c2, copied down:
=IF(ISNUMBER(MATCH(LEFT(A2,FIND("_",A2)-1),$B$2:$B$185,0)),A2,"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
It is an inventory number, the numbers grow from 1 to who knows. That is why i have numbers with 1 character and with 4 (at most right now).
So i need a formula that covers a flexible criteria, up to first 4 characters (numbers & letters).
Hi Glenn,
When i open the document, in the cell is "=NA()".
I entered the formula and: Err:508.
FIND("_",A2)-1 is an unknown language for me, if you are kind t explain
I'll try to open the file you uploaded with Microsoft Excel, i use LibreOffice 5
Thank you
Valentin, You are correct. I attached the wrong file. It was the one that you had uploaded. Try this one. If it is correct, I will explain the formula. if it's wrong, can you explain again what you want.
Re: Compare first 4 characters and if matched extract information from cell
While seeing Glen's post i tried my best to understand the formula and produce a solution.
I stayed on this minimum 3 hours and i still need help.
My belief is that it needs a formula that includes the mathematics of VLOOKUP and LEFT
I attached the example in witch column C shows the desired result.
TryOriginally Posted by valentin.nicolae
C2Formula:
Please Login or Register to view this content.
Above highlighted part make text to numbers. Left function is under text function group and it produce text only. While match range is numbers so that match value should be also number.
Hope this will help you
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
The formula returns the exact picture's name from column A, without comparing the names corectly.
For LibreOffice i changed all "," with ";", resulting =IF(ISNUMBER(MATCH(LEFT(A2;FIND("_";A2)-1)+0;$B$2:$B$187;0));A2;"")
"Above highlighted part make text to numbers"
Please be more specific, i need to understand what "+0" is in order to make changes.
What to change and where?
Thank you Shukla
Last edited by valentin.nicolae; 03-18-2017 at 03:05 AM.
What are your desired detail with explain how???
Quote: "Above highlighted part make text to numbers"
The highlighted part is "+0".
What do i have to do with "+0"? Change what column from text to numbers?
The formula you mentioned gave the result specified in example? If yes, can you attatch the file?
Thank you Shukla
Try
C2Formula:Please Login or Register to view this content.
Try this in C2, copied down
=IF(ISERROR(INDEX($A$2:$A$130,MATCH(B2,--(LEFT($A$2:$A$130,SEARCH("_",$A$2:$A$130)-1)),0))),"Unavailable",INDEX($A$2:$A$130,MATCH(B2,--(LEFT($A$2:$A$130,SEARCH("_",$A$2:$A$130)-1)),0)))
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
I did get confused by your headers in columns C & D. I was not sure which one you wanted. So, I gave you column C. If this is NOT what you want, just show what you DO want, nothing else. If it IS what you want, great. then I can explain it.
Do you know if the function ISERROR is abvailable in LibreOffice? The Excel format you are using .xls is for Excel 97-2003. If you can use ISERROR and .xlsx, my formula can be reduced to:
=ISERROR(INDEX($A$2:$A$130,MATCH(B2,--(LEFT($A$2:$A$130,SEARCH("_",$A$2:$A$130)-1)),0)),"Unavailable")
by the way, you can save a lot of time if you include your expected results (at least some of them) in your original post. I was guessing what your words meant...
Yes, LibreOffice has ISERROR function
SORRY. I meant IFERROR....
Can you open this and does it work?
Re: Compare first 4 characters and if matched extract information from cell
In the example provided LibreOffice returns only the message "Unavailable" and in .xlsx "Err:508".
Probably this is a closed issue, due to software differences.
IFERROR also exists.
In MS Office, attached example returns the expected result only for cells with numbers (4, 10, 82). If the content of the cell has numbers & letters (8B, 25B) retuns "Unavailable" by default. I attached the file with highlighted cells.
In MS Office, .xlsx, when i use the short formula i receive this message: "You've entered too many arguments for this function".
Again, changed "," with ";"
=ISERROR(INDEX($A$2:$A$130;MATCH(B2;--(LEFT($A$2:$A$130;SEARCH("_";$A$2:$A$130)-1));0));"Unavailable")
Thank you Glenn
Same issue.
MS Office works only for cells with numbers, except those with numbers&letters (8B, 25B).
Libre Office returns only "Unavailable"
How about this?
=IFERROR(VLOOKUP(B2&"_"&"*";$A$2:$A$149;1;0);"Unavailable")
Last edited by AliGW; 03-18-2017 at 04:19 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
OK. It's fixed. Check it over and I'll explain...
Don't forget. It's an array formula (CTRL-SHIFT-ENTER)
Ali. Why was I horrendously overcomplicating this?????
Glenn - I have no idea and was thinking the same thing.
Withdrawn by FR. Reason: Same thing Glenn said. I was over complicating things .... again.
Last edited by FlameRetired; 03-18-2017 at 04:23 AM.
Dave
MS Office: works
LibreOffice: delivers only "Unavailable"
For which solution? You now have 3 ... Did you try mine?
Use Ali's formula... But like this:
=IF(ISERROR(VLOOKUP(B2&"_"&"*";$A$2:$A$149;1;0));"Unavailable";VLOOKUP(B2&"_"&"*";$A$2:$A$149;1;0))
Last edited by AliGW; 03-18-2017 at 04:34 AM. Reason: Changed some commas to semi-colons in formula.
MS Office: works
LibreOffice: delivers only "Unavailable"
I wanted to reply with "Quote"
Thank you AliGW
Thanks, Glenn, for the amendment - I am not familiar with Libre Office.
MS Office: works
LibreOffice: works (eureka!)
Works directly, without CTRL-SHIFT-ENTER
Now i'll try to understand formulas now
Could you please let us know if this works?
=IF(ISERROR(VLOOKUP(B2&"_"&"*";$A$2:$A$149;1;0));"Unavailable";VLOOKUP(B2&"_"&"*";$A$2:$A$149;1;0))
A non-array solution will be more efficient on a large dataset.
MS Office: works
LibreOffice: returns only "Unavailable"
Thank you Glenn and AliGW
Thanks for confirming.
Glenn, AliGW.
Please explain: B2&"_"&"*"
Especially "*"
for ISERROR and IFERROR i will use help
Thank you
I was very curious as well
Nicolae,
B2&"_"&"*"
What the above does is it looks at the value of B2 and concatenates it (&) with the _ character and the wildcard *. So this means that Excel will look in column A and find a match that contains the value of B2 plus those items, i.e. 1_ followed by anything.
Let me know if you need any further explanation.
Last edited by AliGW; 03-18-2017 at 09:04 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks