Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract a text string based on character
I am trying to extract a model number from the title.
Brand New D-Link AirPlus G DI-524 Wireless Router; I need to extract the DI-524. All of the model numbers have the - charcter, but are different lengths. How would I use the mid function, not knowing the length and exact position? For instance, here is another title: **New** Acer TravelMate TM4720-6218, 2.2GHz Core 2 Duo; I need the TM4720-6218 extracted from the text string. Thank you for your help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract a text string based on character
How are you entering the title into the cell? A bit of lateral thinking - Can
the title be entered into three adjacent columns with the model number in the middle cell - you could then, in a fourth column, concatentate the title back into one cell! "kgiraffa" wrote: I am trying to extract a model number from the title. Brand New D-Link AirPlus G DI-524 Wireless Router; I need to extract the DI-524. All of the model numbers have the - charcter, but are different lengths. How would I use the mid function, not knowing the length and exact position? For instance, here is another title: **New** Acer TravelMate TM4720-6218, 2.2GHz Core 2 Duo; I need the TM4720-6218 extracted from the text string. Thank you for your help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract a text string based on character
On Thu, 13 Mar 2008 07:39:04 -0700, kgiraffa
wrote: I am trying to extract a model number from the title. Brand New D-Link AirPlus G DI-524 Wireless Router; I need to extract the DI-524. All of the model numbers have the - charcter, but are different lengths. How would I use the mid function, not knowing the length and exact position? For instance, here is another title: **New** Acer TravelMate TM4720-6218, 2.2GHz Core 2 Duo; I need the TM4720-6218 extracted from the text string. Thank you for your help! With the information you have given, it is not possible without manual editing. Your description of a model number -- having the "-" character -- does not return only model numbers. For example, in your first example, a routine that would return words with "-"'s would also return D-Link. If we could be guaranteed that the model designation will always be the LAST word in the string that contains a "-", and also that there will not be an <space within the model designation, then this UDF can be used to return it: ================================ Option Explicit Function Model(str As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\b\S+-\S+\b" If re.test(str) = True Then Set mc = re.Execute(str) Model = mc(mc.Count - 1) End If End Function ================================ To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code above into the window that opens. To use this, enter a formula =Model(cell_ref) into some cell where cell_ref refers to the cell containing the string with the model number. This algorithm could be done with worksheet formula, but it is involved and I'd rather wait to see exactly what your specifications are, before going further. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract a text string based on character
That would be great, but it is information that is downloaded from other
websites. This is how the information downloads.....I just want to alter it. Unfortunately, it isn't always in the same spot in the text. I do appreciate your input! "Ron@Buy" wrote: How are you entering the title into the cell? A bit of lateral thinking - Can the title be entered into three adjacent columns with the model number in the middle cell - you could then, in a fourth column, concatentate the title back into one cell! "kgiraffa" wrote: I am trying to extract a model number from the title. Brand New D-Link AirPlus G DI-524 Wireless Router; I need to extract the DI-524. All of the model numbers have the - charcter, but are different lengths. How would I use the mid function, not knowing the length and exact position? For instance, here is another title: **New** Acer TravelMate TM4720-6218, 2.2GHz Core 2 Duo; I need the TM4720-6218 extracted from the text string. Thank you for your help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract a text string based on character
This worked great, Thank you!!!!
"Ron Rosenfeld" wrote: On Thu, 13 Mar 2008 07:39:04 -0700, kgiraffa wrote: I am trying to extract a model number from the title. Brand New D-Link AirPlus G DI-524 Wireless Router; I need to extract the DI-524. All of the model numbers have the - charcter, but are different lengths. How would I use the mid function, not knowing the length and exact position? For instance, here is another title: **New** Acer TravelMate TM4720-6218, 2.2GHz Core 2 Duo; I need the TM4720-6218 extracted from the text string. Thank you for your help! With the information you have given, it is not possible without manual editing. Your description of a model number -- having the "-" character -- does not return only model numbers. For example, in your first example, a routine that would return words with "-"'s would also return D-Link. If we could be guaranteed that the model designation will always be the LAST word in the string that contains a "-", and also that there will not be an <space within the model designation, then this UDF can be used to return it: ================================ Option Explicit Function Model(str As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\b\S+-\S+\b" If re.test(str) = True Then Set mc = re.Execute(str) Model = mc(mc.Count - 1) End If End Function ================================ To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code above into the window that opens. To use this, enter a formula =Model(cell_ref) into some cell where cell_ref refers to the cell containing the string with the model number. This algorithm could be done with worksheet formula, but it is involved and I'd rather wait to see exactly what your specifications are, before going further. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract a text string based on character
On Thu, 13 Mar 2008 16:55:01 -0700, kgiraffa
wrote: This worked great, Thank you!!!! Well, I'm glad it worked for you. I guess my assumptions about your data were correct. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract text from a string | Excel Worksheet Functions | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
Extract String based on condition | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions |