| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Text with Left, Mid, etc
I have 2 spreadsheets that need to talk to each other. One is a product line
plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
Try
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
Is it really that simple? That works perfectly! Thank you very much, Jacob.
"Jacob Skaria" wrote: Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
Oops, I spoke too soon. Your formula puts a space at the end of the text.
That defeats the possiblity of a VLOOKUP. Sorry. "Jacob Skaria" wrote: Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
=TRIM(LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1))) berniean wrote: Oops, I spoke too soon. Your formula puts a space at the end of the text. That defeats the possiblity of a VLOOKUP. Sorry. "Jacob Skaria" wrote: Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
Try the below
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: Oops, I spoke too soon. Your formula puts a space at the end of the text. That defeats the possiblity of a VLOOKUP. Sorry. "Jacob Skaria" wrote: Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
Yes, that works. As does Glenn's Trim function.
"Jacob Skaria" wrote: Try the below =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: Oops, I spoke too soon. Your formula puts a space at the end of the text. That defeats the possiblity of a VLOOKUP. Sorry. "Jacob Skaria" wrote: Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
Yes, that works, as does Jacob's -1)
"Glenn" wrote: =TRIM(LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1))) berniean wrote: Oops, I spoke too soon. Your formula puts a space at the end of the text. That defeats the possiblity of a VLOOKUP. Sorry. "Jacob Skaria" wrote: Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract Text with Left, Mid, etc
Try using
=MID(A9,1,FIND(" ",A9,FIND(" ",A9)+1)) Anand 09910548139 On Oct 2, 12:11*pm, berniean wrote: Yes, that works. As does Glenn's Trim function. "Jacob Skaria" wrote: Try the below =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: Oops, I spoke too soon. Your formula puts a space at the end of the text. That defeats the possiblity of a VLOOKUP. Sorry. "Jacob Skaria" wrote: Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) If this post helps click Yes --------------- Jacob Skaria "berniean" wrote: I have 2 spreadsheets that need to talk to each other. One is a product line plan, the other is a margin model. I need to compare product names on the two and show any differences on the margin model. The problem is, some of the products have extra text in the form of size in the margin model, but not in the line plan. This is necessary because costs will be different for different sizes. All the names have 3 words, the third being the size. Margin Model Name will be: * Product One XS Product One Small Product One Medium Product Three XS Product Three Small Product Three Medium Line plan name will be Product One, Product Two, Product Three, etc. Assuming margin model name in column A, this is the formula I came up with just to get the text: =LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6) This will return Product One, and even Product Two, but not Product Three, instead yielding Product Thre. Since the second word is of variable length and the size text is variable, how do I eliminate the size word and just get the two Product name words? Once I have that, I can do an IF statement with a VLOOKUP to compare the two names and return any discrepancies. Any help greatly appreciated! Bernie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to extract left-most number from a string | Excel Worksheet Functions | |||
| to extract numeric figures in left side od the alpha charectors pl | Excel Discussion (Misc queries) | |||
| Easiest way to extract characters in a cell LEFT or RIGHT of a sym | Excel Worksheet Functions | |||
| Find text within cell then display text to left | Excel Discussion (Misc queries) | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
Linear Mode
