partial match excel with different languages
I have 2 different columns in excel and both are containing address lines but in different languages 1 in English and 2nd in Japanese, How can I do the partial match just the based upon the last numeric numbers of both the address.
Please take a look in the attachment to better understand.
My data set looks like this
excel excel-formula
asked Nov 12 at 10:32
Kamal Prajapati
12
add a comment |
I have 2 different columns in excel and both are containing address lines but in different languages 1 in English and 2nd in Japanese, How can I do the partial match just the based upon the last numeric numbers of both the address.
Please take a look in the attachment to better understand.
My data set looks like this
excel excel-formula
asked Nov 12 at 10:32
Kamal Prajapati
12
copy your data from excel and paste here so that people can test any formula on that data.
– usmanhaq
Nov 13 at 14:12
add a comment |
I have 2 different columns in excel and both are containing address lines but in different languages 1 in English and 2nd in Japanese, How can I do the partial match just the based upon the last numeric numbers of both the address.
Please take a look in the attachment to better understand.
My data set looks like this
excel excel-formula
asked Nov 12 at 10:32
Kamal Prajapati
12
I have 2 different columns in excel and both are containing address lines but in different languages 1 in English and 2nd in Japanese, How can I do the partial match just the based upon the last numeric numbers of both the address.
Please take a look in the attachment to better understand.
My data set looks like this
excel excel-formula
excel excel-formula
asked Nov 12 at 10:32
Kamal Prajapati
12
asked Nov 12 at 10:32
Kamal Prajapati
12
edited Nov 12 at 10:59
asked Nov 12 at 10:32
Kamal Prajapati
12
asked Nov 12 at 10:32
Kamal Prajapati
12
asked Nov 12 at 10:32
Kamal Prajapati
12
12
copy your data from excel and paste here so that people can test any formula on that data.
– usmanhaq
Nov 13 at 14:12
add a comment |
copy your data from excel and paste here so that people can test any formula on that data.
– usmanhaq
Nov 13 at 14:12
copy your data from excel and paste here so that people can test any formula on that data.
– usmanhaq
Nov 13 at 14:12
copy your data from excel and paste here so that people can test any formula on that data.
– usmanhaq
Nov 13 at 14:12
add a comment |
2 Answers
2
active
oldest
votes
So, first issue is address 1 does not have a number on the right.
Now, for address 2 you could use :
=right(B1,8)
which will bring back the last 8 characters ie the 7 digits with the hyphen. You could match to that result as you wish, assuming the data for adrress 2 is in column B.
Image as proof:
answered Nov 12 at 12:20
Solar Mike
2,0222514
I had tried this but each row having the different length for numeric digits due to this it wouldn't work out. do you have any another way to do it?
– Kamal Prajapati
Nov 13 at 13:53
Well, I have just thrown together some data in cells to make sure it works, see the image, not sure what is going wrong for you.
– Solar Mike
Nov 13 at 14:01
add a comment |
As per your data sample, in English address the number you are looking for is separated by comma and is coming at start, For example in 3-10-31, AMAVAMA, you can look for 3-10-31 which is separated by a comma from rest of the address
If this is the case in most of the cells you can use the below formula
Considering your Address 1 is in column B, Address 2 is in column C and the formula is in column A
=INDEX($C$2:$C$5,MATCH(1,IF(IFERROR(SEARCH(LEFT(B2,FIND(",",B2)-1),$C$2:$C$5),0)>0,1,0),0))
the formula is in cell A2 and is an array formula
answered Nov 13 at 14:40
usmanhaq
1,123128
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53260294%2fpartial-match-excel-with-different-languages%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
So, first issue is address 1 does not have a number on the right.
Now, for address 2 you could use :
=right(B1,8)
which will bring back the last 8 characters ie the 7 digits with the hyphen. You could match to that result as you wish, assuming the data for adrress 2 is in column B.
Image as proof:
answered Nov 12 at 12:20
Solar Mike
2,0222514
I had tried this but each row having the different length for numeric digits due to this it wouldn't work out. do you have any another way to do it?
– Kamal Prajapati
Nov 13 at 13:53
Well, I have just thrown together some data in cells to make sure it works, see the image, not sure what is going wrong for you.
– Solar Mike
Nov 13 at 14:01
add a comment |
So, first issue is address 1 does not have a number on the right.
Now, for address 2 you could use :
=right(B1,8)
which will bring back the last 8 characters ie the 7 digits with the hyphen. You could match to that result as you wish, assuming the data for adrress 2 is in column B.
Image as proof:
answered Nov 12 at 12:20
Solar Mike
2,0222514
I had tried this but each row having the different length for numeric digits due to this it wouldn't work out. do you have any another way to do it?
– Kamal Prajapati
Nov 13 at 13:53
Well, I have just thrown together some data in cells to make sure it works, see the image, not sure what is going wrong for you.
– Solar Mike
Nov 13 at 14:01
add a comment |
So, first issue is address 1 does not have a number on the right.
Now, for address 2 you could use :
=right(B1,8)
which will bring back the last 8 characters ie the 7 digits with the hyphen. You could match to that result as you wish, assuming the data for adrress 2 is in column B.
Image as proof:
answered Nov 12 at 12:20
Solar Mike
2,0222514
So, first issue is address 1 does not have a number on the right.
Now, for address 2 you could use :
=right(B1,8)
which will bring back the last 8 characters ie the 7 digits with the hyphen. You could match to that result as you wish, assuming the data for adrress 2 is in column B.
Image as proof:
answered Nov 12 at 12:20
Solar Mike
2,0222514
edited Nov 13 at 14:00
answered Nov 12 at 12:20
Solar Mike
2,0222514
answered Nov 12 at 12:20
Solar Mike
2,0222514
answered Nov 12 at 12:20
Solar Mike
2,0222514
2,0222514
I had tried this but each row having the different length for numeric digits due to this it wouldn't work out. do you have any another way to do it?
– Kamal Prajapati
Nov 13 at 13:53
Well, I have just thrown together some data in cells to make sure it works, see the image, not sure what is going wrong for you.
– Solar Mike
Nov 13 at 14:01
add a comment |
I had tried this but each row having the different length for numeric digits due to this it wouldn't work out. do you have any another way to do it?
– Kamal Prajapati
Nov 13 at 13:53
Well, I have just thrown together some data in cells to make sure it works, see the image, not sure what is going wrong for you.
– Solar Mike
Nov 13 at 14:01
I had tried this but each row having the different length for numeric digits due to this it wouldn't work out. do you have any another way to do it?
– Kamal Prajapati
Nov 13 at 13:53
I had tried this but each row having the different length for numeric digits due to this it wouldn't work out. do you have any another way to do it?
– Kamal Prajapati
Nov 13 at 13:53
Well, I have just thrown together some data in cells to make sure it works, see the image, not sure what is going wrong for you.
– Solar Mike
Nov 13 at 14:01
Well, I have just thrown together some data in cells to make sure it works, see the image, not sure what is going wrong for you.
– Solar Mike
Nov 13 at 14:01
add a comment |
As per your data sample, in English address the number you are looking for is separated by comma and is coming at start, For example in 3-10-31, AMAVAMA, you can look for 3-10-31 which is separated by a comma from rest of the address
If this is the case in most of the cells you can use the below formula
Considering your Address 1 is in column B, Address 2 is in column C and the formula is in column A
=INDEX($C$2:$C$5,MATCH(1,IF(IFERROR(SEARCH(LEFT(B2,FIND(",",B2)-1),$C$2:$C$5),0)>0,1,0),0))
the formula is in cell A2 and is an array formula
answered Nov 13 at 14:40
usmanhaq
1,123128
add a comment |
As per your data sample, in English address the number you are looking for is separated by comma and is coming at start, For example in 3-10-31, AMAVAMA, you can look for 3-10-31 which is separated by a comma from rest of the address
If this is the case in most of the cells you can use the below formula
Considering your Address 1 is in column B, Address 2 is in column C and the formula is in column A
=INDEX($C$2:$C$5,MATCH(1,IF(IFERROR(SEARCH(LEFT(B2,FIND(",",B2)-1),$C$2:$C$5),0)>0,1,0),0))
the formula is in cell A2 and is an array formula
answered Nov 13 at 14:40
usmanhaq
1,123128
add a comment |
As per your data sample, in English address the number you are looking for is separated by comma and is coming at start, For example in 3-10-31, AMAVAMA, you can look for 3-10-31 which is separated by a comma from rest of the address
If this is the case in most of the cells you can use the below formula
Considering your Address 1 is in column B, Address 2 is in column C and the formula is in column A
=INDEX($C$2:$C$5,MATCH(1,IF(IFERROR(SEARCH(LEFT(B2,FIND(",",B2)-1),$C$2:$C$5),0)>0,1,0),0))
the formula is in cell A2 and is an array formula
answered Nov 13 at 14:40
usmanhaq
1,123128
As per your data sample, in English address the number you are looking for is separated by comma and is coming at start, For example in 3-10-31, AMAVAMA, you can look for 3-10-31 which is separated by a comma from rest of the address
If this is the case in most of the cells you can use the below formula
Considering your Address 1 is in column B, Address 2 is in column C and the formula is in column A
=INDEX($C$2:$C$5,MATCH(1,IF(IFERROR(SEARCH(LEFT(B2,FIND(",",B2)-1),$C$2:$C$5),0)>0,1,0),0))
the formula is in cell A2 and is an array formula
answered Nov 13 at 14:40
usmanhaq
1,123128
answered Nov 13 at 14:40
usmanhaq
1,123128
answered Nov 13 at 14:40
usmanhaq
1,123128
answered Nov 13 at 14:40
usmanhaq
1,123128
1,123128
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53260294%2fpartial-match-excel-with-different-languages%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
copy your data from excel and paste here so that people can test any formula on that data.
– usmanhaq
Nov 13 at 14:12