find and extract space??space??space

[keysersozeh]

I'm looking of a solution to find and extract a certain string from a column with product titles.

Example:
-------------------
Bumper voor Audi A3 8 L 96 00 voorzijde
Bumper voor Audi A3 8 P 00 03 voorzijde
-------------------

What to extract:
space??space??space
Which should return:
-------------------
09 00
00 03
-------------------

I hope i'm clear, and hope someone could help me out. Thanks.

 

[FormR]

What to extract:
space??space??space

Hi, if that pattern holds true for all of your data you can try:

Excel 2013/2016
	A	B
1	Bumper voor Audi A3 8 L 96 00 voorzijde	96 00
2	Bumper voor Audi A3 8 P 00 03 voorzijde	00 03
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,SEARCH(" ?? ?? ",A1)+1,5)

 

[keysersozeh]

Hi, if that pattern holds true for all of your data you can try:

Excel 2013/2016
	A	B
1	Bumper voor Audi A3 8 L 96 00 voorzijde	96 00
2	Bumper voor Audi A3 8 P 00 03 voorzijde	00 03
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,SEARCH(" ?? ?? ",A1)+1,5)

Worked like a charm. For learning purposes, what does the +1,5 do?

Thanks FormR!

 

[FormR]

Glad to help, welcome to the forum btw

For learning purposes, what does the +1,5 do?

Search returns the position of the space??space??space in the string and you want to start returning text +1 position to the right of that, as you don't want the first space in your results. After that we want to return 5 positions of text - i.e. the ??space??

Hope that helps.

 

[Peter_SSs]

Welcome to the MrExcel board!

Worked like a charm.

Don't know if it is possible with your data, but could you be caught out by having this in column A?

Bumper voor VW A3 8 L 96 00 voorzijde

If so perhaps you could just extend the search a bit further left and look for 2 single characters before that as well?

=MID(A1,SEARCH(" ? ? ?? ?? ",A1)+5,5)

Otherwise, if the text in question was always followed by a single word like your 2 examples

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),300),200))

 

[keysersozeh]

Welcome to the MrExcel board!

Don't know if it is possible with your data, but could you be caught out by having this in column A?

Bumper voor VW A3 8 L 96 00 voorzijde

If so perhaps you could just extend the search a bit further left and look for 2 single characters before that as well?

=MID(A1,SEARCH(" ? ? ?? ?? ",A1)+5,5)

Otherwise, if the text in question was always followed by a single word like your 2 examples

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),300),200))

Thanks for that!

The only big issue i get with FormR's solution is with certain car model types, example:
Bumper voor Jeep Cherokee Xj 91 96 voorzijde Links

the data i get back in that case is: Xj 91

A possible solution for this is if the formula could be altered to start looking from the right intstead of from the left, or to espcape every 2 characters that contain one or more letter(s), or a combination of both.

Thanks again guys, big help!

 

[Rick Rothstein]

Here is a UDF (user defined function) that will return the two digits followed by a space followed by two digits embedded within a substring of space followed by two digits followed by a space followed by two digits followed by a space...

Function DDSDD(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 7) Like " ## ## " Then
      DDSDD = Mid(S, X + 1, 5)
      Exit For
    End If
  Next
End Function

Note: Just so you know, I named the function DDSDD because it is short for Digit Digit Space Digit Digit.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DDSDD just like it was a built-in Excel function. For example,

=DDSDD(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rick Rothstein]

Here is a UDF (user defined function) that will return the two digits followed by a space followed by two digits embedded within a substring of space followed by two digits followed by a space followed by two digits followed by a space...

Function DDSDD(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 7) Like " ## ## " Then
      DDSDD = Mid(S, X + 1, 5)
      Exit For
    End If
  Next
End Function

Note: Just so you know, I named the function DDSDD because it is short for Digit Digit Space Digit Digit.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DDSDD just like it was a built-in Excel function. For example,

=DDSDD(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Same installation instructions, but this code is a little better as it searches for the pattern starting from the end of the text rather than the beginning of the text...

Function DDSDD(S As String) As String
  Dim X As Long
  For X = Len(S) - 6 To 1 Step -1
    If Mid(S, X, 7) Like " ## ## " Then
      DDSDD = Mid(S, X + 1, 5)
      Exit For
    End If
  Next
End Function