Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
I believe these problems have the same solution.
1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
Try these:
1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
Biff,
can you tell me what the " does in the solution? your solutions worked perfectly! thank you! very much appreciated :) jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
and sorry I didn't combine my questions but for the 2nd solution also.... if
'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
=SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0
This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
Ooops! Correction needed:
In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
that was so clear - thank you so much for the explanation!
take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
You're welcome. Thanks for the feedback!
Biff "Jane" wrote in message ... that was so clear - thank you so much for the explanation! take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
Hi Biff,
I shared your solutions with a co-worker who also had a similar situation. She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? thank you, Jane "T. Valko" wrote: You're welcome. Thanks for the feedback! Biff "Jane" wrote in message ... that was so clear - thank you so much for the explanation! take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
She was going to approach the problem with =RIGHT
but include LEN.. she wanted me to ask you if that might also work? To do what exactly? Biff "Jane" wrote in message ... Hi Biff, I shared your solutions with a co-worker who also had a similar situation. She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? thank you, Jane "T. Valko" wrote: You're welcome. Thanks for the feedback! Biff "Jane" wrote in message ... that was so clear - thank you so much for the explanation! take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
Hi there,
So sorry I was clearer... for problem #w, she had asked if there was a way to use =RIGHT and the LEN function. Since that morning note, she actually tried: =LEFT(C5,LEN(C5)-16) and it produced the same results. She wanted to know if this is a flexible enough solution to use in teh same way we used your solution below. take care, Jane "T. Valko" wrote: She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? To do what exactly? Biff "Jane" wrote in message ... Hi Biff, I shared your solutions with a co-worker who also had a similar situation. She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? thank you, Jane "T. Valko" wrote: You're welcome. Thanks for the feedback! Biff "Jane" wrote in message ... that was so clear - thank you so much for the explanation! take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
=LEFT(C5,LEN(C5)-16) and it produced the same results
As long as *every* entry is the same format that will work just fine. For example, that method would not work on these: TRAD EC JEANS (S) | CL119410 TRAD EC JEANS (S) CL1941033 TRAD EC JEANS (S) | CL11 That's when using: =LEFT(A1,FIND("(",A1)-2), is the best solution. Also, since you only posted a single example I offered what I believe to be the most versatile solution based on the limited information available. Biff "Jane" wrote in message ... Hi there, So sorry I was clearer... for problem #w, she had asked if there was a way to use =RIGHT and the LEN function. Since that morning note, she actually tried: =LEFT(C5,LEN(C5)-16) and it produced the same results. She wanted to know if this is a flexible enough solution to use in teh same way we used your solution below. take care, Jane "T. Valko" wrote: She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? To do what exactly? Biff "Jane" wrote in message ... Hi Biff, I shared your solutions with a co-worker who also had a similar situation. She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? thank you, Jane "T. Valko" wrote: You're welcome. Thanks for the feedback! Biff "Jane" wrote in message ... that was so clear - thank you so much for the explanation! take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
ah gotcha Biff. makes total sense.... I agree that yours is more versatile.
Since there are often so many moving parts that, I agree, it is the best solution - will pass on the info. Again, very much appreciate your help and time!! take good care, Jane "T. Valko" wrote: =LEFT(C5,LEN(C5)-16) and it produced the same results As long as *every* entry is the same format that will work just fine. For example, that method would not work on these: TRAD EC JEANS (S) | CL119410 TRAD EC JEANS (S) CL1941033 TRAD EC JEANS (S) | CL11 That's when using: =LEFT(A1,FIND("(",A1)-2), is the best solution. Also, since you only posted a single example I offered what I believe to be the most versatile solution based on the limited information available. Biff "Jane" wrote in message ... Hi there, So sorry I was clearer... for problem #w, she had asked if there was a way to use =RIGHT and the LEN function. Since that morning note, she actually tried: =LEFT(C5,LEN(C5)-16) and it produced the same results. She wanted to know if this is a flexible enough solution to use in teh same way we used your solution below. take care, Jane "T. Valko" wrote: She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? To do what exactly? Biff "Jane" wrote in message ... Hi Biff, I shared your solutions with a co-worker who also had a similar situation. She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? thank you, Jane "T. Valko" wrote: You're welcome. Thanks for the feedback! Biff "Jane" wrote in message ... that was so clear - thank you so much for the explanation! take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing value within a string
You're very welcome!
Biff "Jane" wrote in message ... ah gotcha Biff. makes total sense.... I agree that yours is more versatile. Since there are often so many moving parts that, I agree, it is the best solution - will pass on the info. Again, very much appreciate your help and time!! take good care, Jane "T. Valko" wrote: =LEFT(C5,LEN(C5)-16) and it produced the same results As long as *every* entry is the same format that will work just fine. For example, that method would not work on these: TRAD EC JEANS (S) | CL119410 TRAD EC JEANS (S) CL1941033 TRAD EC JEANS (S) | CL11 That's when using: =LEFT(A1,FIND("(",A1)-2), is the best solution. Also, since you only posted a single example I offered what I believe to be the most versatile solution based on the limited information available. Biff "Jane" wrote in message ... Hi there, So sorry I was clearer... for problem #w, she had asked if there was a way to use =RIGHT and the LEN function. Since that morning note, she actually tried: =LEFT(C5,LEN(C5)-16) and it produced the same results. She wanted to know if this is a flexible enough solution to use in teh same way we used your solution below. take care, Jane "T. Valko" wrote: She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? To do what exactly? Biff "Jane" wrote in message ... Hi Biff, I shared your solutions with a co-worker who also had a similar situation. She was going to approach the problem with =RIGHT but include LEN.. she wanted me to ask you if that might also work? thank you, Jane "T. Valko" wrote: You're welcome. Thanks for the feedback! Biff "Jane" wrote in message ... that was so clear - thank you so much for the explanation! take care, Jane "T. Valko" wrote: Ooops! Correction needed: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. Should be: In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the LEFT of that. Biff "T. Valko" wrote in message ... =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 This formula is simply replacing the 3 characters from the right with the 2 characters from the right. If: A1 = 66014 Replace 3 characters from the right = 014 With 2 characters from the right = 14 Result = 6614 The result of the Substitute function is always a TEXT value. The +0 coerces the result to be NUMERIC. The second formula: =LEFT(A1,FIND("(",A1)-2) I'm assuming that the portion of the string that you want to extract is variable in length and that this portion is always present: (S) TRAD EC JEANS (S) | CL11941033 In that case all we need to do is find the location of the opening parenthesis "(" in the string and extract everything to the right of that. In this example the "(" is the 15th character. So we subtract 2, one for the "(" and one for the space before the "(". That results in 13. So the formula is returning the first 13 characters starting from the left. Biff "Jane" wrote in message ... and sorry I didn't combine my questions but for the 2nd solution also.... if 'from the right is need, why go with LEFT? and how does the -2 work into it? I just want to be able to understand it so I can explain it to some one else if asked and alter if needed for future use. Thank you again! Jane "T. Valko" wrote: Try these: 1st problem: =SUBSTITUTE(A1,RIGHT(A1,3),RIGHT(A1,2))+0 2nd problem: =LEFT(A1,FIND("(",A1)-2) Biff "Jane" wrote in message ... I believe these problems have the same solution. 1st problem: I would like to be able to remove the zero that is placed 3 digits from the RIGHT. The values a 66014 8004 8015 The values should look like: 6614 804 815 2nd problem in the same mproject: I would like to be able to remove the values, text, and/ or spaces that are 16 places from the RIGHT. The entry is: TRAD EC JEANS (S) | CL11941033 The entry should look like: TRAD EC JEANS thank you for your help! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help with removing text string | Excel Discussion (Misc queries) | |||
removing part of text string to another cell | Excel Discussion (Misc queries) | |||
Removing Spaces from string | Excel Worksheet Functions | |||
NEED HELP-----Removing a space at the end of a string of character | Excel Discussion (Misc queries) | |||
Removing numbers from the beginning of a text string | Excel Worksheet Functions |