vlookup function excel cut part of string
0
Guys i have a little problem. I have 2 csv files, i want to copy some data from one csv to another where id is the same. For this i use vlookupfunction but something is not good.
The orginal string in orginal csv is:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
And when i use this function
=IFERROR(VLOOKUP($A2,osnova.csv!$B$2:$AD$1660,8,0),IF(G2="","",G2))
I get this string:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
The difference is that i have in orginal string this part and in the copied version i lose that part:
.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP.
Can someone help me with this? Did i do something wrong in my function?
excel vlookup
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
|
show 5 more comments
0
Guys i have a little problem. I have 2 csv files, i want to copy some data from one csv to another where id is the same. For this i use vlookupfunction but something is not good.
The orginal string in orginal csv is:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
And when i use this function
=IFERROR(VLOOKUP($A2,osnova.csv!$B$2:$AD$1660,8,0),IF(G2="","",G2))
I get this string:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
The difference is that i have in orginal string this part and in the copied version i lose that part:
.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP.
Can someone help me with this? Did i do something wrong in my function?
excel vlookup
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
Is the function hitting a character limit?
– Solar Mike
Nov 24 '18 at 10:46
How do you mean? I didnt know for character limit?
– user9819807
Nov 24 '18 at 10:47
So use len() to check the number of characters in the source and result and then check if vlookup has a limit...
– Solar Mike
Nov 24 '18 at 10:48
YES there is difference in length orginal = 612characters copy = 541 characters
– user9819807
Nov 24 '18 at 10:53
@doki I cannot reproduce your problem by using dummy ID's. The entire 612 length string is returned by VLOOKUP. I suspect the issue is with your data. Perhaps G2 contains the string that is being returned by your function?
– Ron Rosenfeld
Nov 24 '18 at 12:00
|
show 5 more comments
0
0
0
Guys i have a little problem. I have 2 csv files, i want to copy some data from one csv to another where id is the same. For this i use vlookupfunction but something is not good.
The orginal string in orginal csv is:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
And when i use this function
=IFERROR(VLOOKUP($A2,osnova.csv!$B$2:$AD$1660,8,0),IF(G2="","",G2))
I get this string:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
The difference is that i have in orginal string this part and in the copied version i lose that part:
.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP.
Can someone help me with this? Did i do something wrong in my function?
excel vlookup
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
Guys i have a little problem. I have 2 csv files, i want to copy some data from one csv to another where id is the same. For this i use vlookupfunction but something is not good.
The orginal string in orginal csv is:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
And when i use this function
=IFERROR(VLOOKUP($A2,osnova.csv!$B$2:$AD$1660,8,0),IF(G2="","",G2))
I get this string:
48 Port Managed Layer 3 Gigabit Ethernet Switch with optional 10GigE uplink and 802.3af and Legacy Power over Ethernet. Includes 48 - Copper Gigabit (1000Base-T) access ports and 2 - High Speed Expansion Slots. Provides up to 370 watts of 802.3af compliant power. Features include 802.1Q VLANs, GVRP, 802.1p QoS, 802.1w Rapid Spanning Tree, 802. 19" Rackmount 1U housing. Includes AC PoE power supply. Supported expansion modules: Dual Stacking XIM (4700470F1, 4700470F2, 4700470F5), Dual SFP XIM (1700473F1), Dual SFP+ XIM (1700471F1).
The difference is that i have in orginal string this part and in the copied version i lose that part:
.3ad Link Aggregation, Auto MDI/MDI-X, CLI, HTTP GUI, SSH, SSL, RADIUS, SNMP.
Can someone help me with this? Did i do something wrong in my function?
excel vlookup
excel vlookup
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
edited Nov 24 '18 at 11:32
Ron Rosenfeld
23.6k41637
23.6k41637
asked Nov 24 '18 at 10:42
user9819807
Is the function hitting a character limit?
– Solar Mike
Nov 24 '18 at 10:46
How do you mean? I didnt know for character limit?
– user9819807
Nov 24 '18 at 10:47
So use len() to check the number of characters in the source and result and then check if vlookup has a limit...
– Solar Mike
Nov 24 '18 at 10:48
YES there is difference in length orginal = 612characters copy = 541 characters
– user9819807
Nov 24 '18 at 10:53
@doki I cannot reproduce your problem by using dummy ID's. The entire 612 length string is returned by VLOOKUP. I suspect the issue is with your data. Perhaps G2 contains the string that is being returned by your function?
– Ron Rosenfeld
Nov 24 '18 at 12:00
|
show 5 more comments
Is the function hitting a character limit?
– Solar Mike
Nov 24 '18 at 10:46
How do you mean? I didnt know for character limit?
– user9819807
Nov 24 '18 at 10:47
So use len() to check the number of characters in the source and result and then check if vlookup has a limit...
– Solar Mike
Nov 24 '18 at 10:48
YES there is difference in length orginal = 612characters copy = 541 characters
– user9819807
Nov 24 '18 at 10:53
@doki I cannot reproduce your problem by using dummy ID's. The entire 612 length string is returned by VLOOKUP. I suspect the issue is with your data. Perhaps G2 contains the string that is being returned by your function?
– Ron Rosenfeld
Nov 24 '18 at 12:00
Is the function hitting a character limit?
– Solar Mike
Nov 24 '18 at 10:46
Is the function hitting a character limit?
– Solar Mike
Nov 24 '18 at 10:46
How do you mean? I didnt know for character limit?
– user9819807
Nov 24 '18 at 10:47
How do you mean? I didnt know for character limit?
– user9819807
Nov 24 '18 at 10:47
So use len() to check the number of characters in the source and result and then check if vlookup has a limit...
– Solar Mike
Nov 24 '18 at 10:48
So use len() to check the number of characters in the source and result and then check if vlookup has a limit...
– Solar Mike
Nov 24 '18 at 10:48
YES there is difference in length orginal = 612characters copy = 541 characters
– user9819807
Nov 24 '18 at 10:53
YES there is difference in length orginal = 612characters copy = 541 characters
– user9819807
Nov 24 '18 at 10:53
@doki I cannot reproduce your problem by using dummy ID's. The entire 612 length string is returned by VLOOKUP. I suspect the issue is with your data. Perhaps G2 contains the string that is being returned by your function?
– Ron Rosenfeld
Nov 24 '18 at 12:00
@doki I cannot reproduce your problem by using dummy ID's. The entire 612 length string is returned by VLOOKUP. I suspect the issue is with your data. Perhaps G2 contains the string that is being returned by your function?
– Ron Rosenfeld
Nov 24 '18 at 12:00
|
show 5 more comments
1 Answer
1
active
oldest
votes
1
Your version of Excel must be hitting the character limit of VLOOKUP. You should not be using this function in the first place, it's broken and it sucks. Consider using much superior INDEX/MATCH combination =index(osnova.csv!$H$1660, match($A2,osnova.csv!$B$2,0)).
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
this index match that you write does not work
– user9819807
Nov 24 '18 at 11:04
@doki You should be able to edit that so it does work, hint vlookup needed column 8 for its result, so the array for index needs to be that result column...
– Solar Mike
Nov 24 '18 at 11:14
@doki - you are right, I have corrected it and it just highlights one many problems with this functions - it makes it difficult to read. Solar Mike have pointed you to the right direction.
– Michal Rosa
Nov 24 '18 at 11:17
VLOOKUP's character limit is inlookup_valueand, if exceeded, returns a#VALUE!error. It does not remove characters in the middle of a returned string.
– Ron Rosenfeld
Nov 24 '18 at 12:46
this formula is not correct
– user9819807
Nov 24 '18 at 13:42
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
});
}
});
draft saved
draft discarded
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%2f53457323%2fvlookup-function-excel-cut-part-of-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Your version of Excel must be hitting the character limit of VLOOKUP. You should not be using this function in the first place, it's broken and it sucks. Consider using much superior INDEX/MATCH combination =index(osnova.csv!$H$1660, match($A2,osnova.csv!$B$2,0)).
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
this index match that you write does not work
– user9819807
Nov 24 '18 at 11:04
@doki You should be able to edit that so it does work, hint vlookup needed column 8 for its result, so the array for index needs to be that result column...
– Solar Mike
Nov 24 '18 at 11:14
@doki - you are right, I have corrected it and it just highlights one many problems with this functions - it makes it difficult to read. Solar Mike have pointed you to the right direction.
– Michal Rosa
Nov 24 '18 at 11:17
VLOOKUP's character limit is inlookup_valueand, if exceeded, returns a#VALUE!error. It does not remove characters in the middle of a returned string.
– Ron Rosenfeld
Nov 24 '18 at 12:46
this formula is not correct
– user9819807
Nov 24 '18 at 13:42
add a comment |
1
Your version of Excel must be hitting the character limit of VLOOKUP. You should not be using this function in the first place, it's broken and it sucks. Consider using much superior INDEX/MATCH combination =index(osnova.csv!$H$1660, match($A2,osnova.csv!$B$2,0)).
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
this index match that you write does not work
– user9819807
Nov 24 '18 at 11:04
@doki You should be able to edit that so it does work, hint vlookup needed column 8 for its result, so the array for index needs to be that result column...
– Solar Mike
Nov 24 '18 at 11:14
@doki - you are right, I have corrected it and it just highlights one many problems with this functions - it makes it difficult to read. Solar Mike have pointed you to the right direction.
– Michal Rosa
Nov 24 '18 at 11:17
VLOOKUP's character limit is inlookup_valueand, if exceeded, returns a#VALUE!error. It does not remove characters in the middle of a returned string.
– Ron Rosenfeld
Nov 24 '18 at 12:46
this formula is not correct
– user9819807
Nov 24 '18 at 13:42
add a comment |
1
1
1
Your version of Excel must be hitting the character limit of VLOOKUP. You should not be using this function in the first place, it's broken and it sucks. Consider using much superior INDEX/MATCH combination =index(osnova.csv!$H$1660, match($A2,osnova.csv!$B$2,0)).
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
Your version of Excel must be hitting the character limit of VLOOKUP. You should not be using this function in the first place, it's broken and it sucks. Consider using much superior INDEX/MATCH combination =index(osnova.csv!$H$1660, match($A2,osnova.csv!$B$2,0)).
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
edited Nov 24 '18 at 11:15
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
answered Nov 24 '18 at 10:59
Michal RosaMichal Rosa
1,3221814
1,3221814
this index match that you write does not work
– user9819807
Nov 24 '18 at 11:04
@doki You should be able to edit that so it does work, hint vlookup needed column 8 for its result, so the array for index needs to be that result column...
– Solar Mike
Nov 24 '18 at 11:14
@doki - you are right, I have corrected it and it just highlights one many problems with this functions - it makes it difficult to read. Solar Mike have pointed you to the right direction.
– Michal Rosa
Nov 24 '18 at 11:17
VLOOKUP's character limit is inlookup_valueand, if exceeded, returns a#VALUE!error. It does not remove characters in the middle of a returned string.
– Ron Rosenfeld
Nov 24 '18 at 12:46
this formula is not correct
– user9819807
Nov 24 '18 at 13:42
add a comment |
this index match that you write does not work
– user9819807
Nov 24 '18 at 11:04
@doki You should be able to edit that so it does work, hint vlookup needed column 8 for its result, so the array for index needs to be that result column...
– Solar Mike
Nov 24 '18 at 11:14
@doki - you are right, I have corrected it and it just highlights one many problems with this functions - it makes it difficult to read. Solar Mike have pointed you to the right direction.
– Michal Rosa
Nov 24 '18 at 11:17
VLOOKUP's character limit is inlookup_valueand, if exceeded, returns a#VALUE!error. It does not remove characters in the middle of a returned string.
– Ron Rosenfeld
Nov 24 '18 at 12:46
this formula is not correct
– user9819807
Nov 24 '18 at 13:42
this index match that you write does not work
– user9819807
Nov 24 '18 at 11:04
this index match that you write does not work
– user9819807
Nov 24 '18 at 11:04
@doki You should be able to edit that so it does work, hint vlookup needed column 8 for its result, so the array for index needs to be that result column...
– Solar Mike
Nov 24 '18 at 11:14
@doki You should be able to edit that so it does work, hint vlookup needed column 8 for its result, so the array for index needs to be that result column...
– Solar Mike
Nov 24 '18 at 11:14
@doki - you are right, I have corrected it and it just highlights one many problems with this functions - it makes it difficult to read. Solar Mike have pointed you to the right direction.
– Michal Rosa
Nov 24 '18 at 11:17
@doki - you are right, I have corrected it and it just highlights one many problems with this functions - it makes it difficult to read. Solar Mike have pointed you to the right direction.
– Michal Rosa
Nov 24 '18 at 11:17
VLOOKUP's character limit is in lookup_value and, if exceeded, returns a #VALUE! error. It does not remove characters in the middle of a returned string.– Ron Rosenfeld
Nov 24 '18 at 12:46
VLOOKUP's character limit is in lookup_value and, if exceeded, returns a #VALUE! error. It does not remove characters in the middle of a returned string.– Ron Rosenfeld
Nov 24 '18 at 12:46
this formula is not correct
– user9819807
Nov 24 '18 at 13:42
this formula is not correct
– user9819807
Nov 24 '18 at 13:42
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53457323%2fvlookup-function-excel-cut-part-of-string%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
Is the function hitting a character limit?
– Solar Mike
Nov 24 '18 at 10:46
How do you mean? I didnt know for character limit?
– user9819807
Nov 24 '18 at 10:47
So use len() to check the number of characters in the source and result and then check if vlookup has a limit...
– Solar Mike
Nov 24 '18 at 10:48
YES there is difference in length orginal = 612characters copy = 541 characters
– user9819807
Nov 24 '18 at 10:53
@doki I cannot reproduce your problem by using dummy ID's. The entire 612 length string is returned by VLOOKUP. I suspect the issue is with your data. Perhaps G2 contains the string that is being returned by your function?
– Ron Rosenfeld
Nov 24 '18 at 12:00