Extract a text string based on character

[kgiraffa]

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!

 

[Ron@Buy]

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!

 

[Ron Rosenfeld]

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

 

[kgiraffa]

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!

 

[kgiraffa]

This worked great, Thank you!!!!

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

 

[Ron Rosenfeld]

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