ColumnA
Springseason
Springseason
Springseasons
ColumnB
Spring Season
Spring season
Spring Season
Result
Spring Season
Spring season
Not Match
…additional ambiguities about what’s your goal;
- Are the texts to be compared row by row or do you want Result even if "ColumnA* text is in row x, and ColumnB is in another row y.
- What to you want to appear if text in ColumnA does not appear in ColumnB
- What to you want to appear if text in ColumnB does not appear in ColumnA
ya, working on libre calc. please check again question edited. it may be small letter also but text length must be same except whitespace
goal;
-
No any relevant column only column A and B, if relavent colX colY is required as id you can create
-
Result columnC = Not Match
-
Olny search columnA text in columnB
Unselect Case sensitive in menu Tools - Options - LibreOffice Calc - Calculate (LibreOffice Help).
Given data in A2 and B2, paste in C2:
=IF(REGEX(A2;" ";"";"g")=REGEX(B2;" ";"";"g");B2;"Not Match")EDIT following @erAck comment. Need to add REGEX to A2 because there are spaces at the end of text.
See sample file Spring Season.ods.
Sample file 2: Spring Season.ods.
REGEX function (available since LibreOffice 6.2). IF function.
You could edit your question, or comment an answer. Do not use Answer to comment.
If the answer solved your question, mark the circle to the left.
This REGEX(B2," ","", ) replaces only the first occurrence of a match, the omitted/missing/empty 4th argument is evaluated as default 1 as if the argument wasn’t given at all. To replace all spaces use REGEX(B2;" ";"";"g")
And btw, in formula expression examples please use the ; semicolon function separator, which is accepted in all locales regardless of settings.
Thanks.
About the semicolon, a few days ago a needed to reset my profile, and this is one of the items to adjust. Just done.
Thanks @LeroyG But i have one doubt can we use it with VLOOKUP
To compare columnA2 text to columnB2:B1000
…so as to compare A2 with every value in B2:B1000?
=IF(REGEX(A$2;" ";"";"g")=REGEX(B2;" ";"";"g");B2;"Not Match")
@LeroyG Above formula searches only A2 in B2:B1000
I want A3 should also search in B2:B1000
likewise A4 in B2:B1000 so…on
can we use it with VLOOKUP To compare columnA2 text to columnB2:B1000
No, because column B contains the spaces. The lookup is done on the data as is. You would have to create a helper column without spaces using the REGEX() function, i.e. in C2 enter REGEX(B2;" ";"";"g") and copy-paste the formula down. You may then also replace the column B data with the column C results by copying the cell range and Paste Special (Shift+Ctrl+V) only the string results onto column B and afterwards delete column C.
You can create a VLOOKUP(REGEX(...);REGEX(B2:B1000;...);...) but those (second) arrays will have to be recalculated for each lookup and thus is not advisable.
A3 should also search in B2:B1000 likewise A4 in B2:B1000 so…on
For that use absolute row references for the lookup range, in C2
=VLOOKUP(REGEX(A2;" ";"";"g");B$2:B$1000;1;0)
and then copy-paste the formula cell down until C1000. (assuming you have replaced the original data in column B with data where the spaces are eliminated).
But then it probably would be better to replace the data in column A as well with spaces eliminated so the REGEX() can be omitted from the LOOKUP() there as well.