1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Extraction of Part Numbers from Text Strings

    From tb@21:1/5 to All on Fri May 8 20:49:06 2020
    I need to extract part numbers from text strings that look like this:

    Example 1:
    J72 5-40 SOLENOID VALVE (there is a double space between J72
    and SOLENOID)

    Example 2:
    SOLENOID VALVE K77 8-32 (there is a double space between VALVE
    and K77)

    Example 3:
    SOLENOID VALVE _X37 1-22 X Y Z (there is an underscore before X37
    and there are 2+ spaces between _X37 and whatever comes after that)

    The formula that I am looking for would extract the part numbers as follows: Example 1: J72 5-40
    Example 2: K77 8-32
    Example 3: X37 1-22 (i.e. w/o the underscore and w/o whatever comes
    after the 2+ spaces)

    Could somebody please help? Would prefer an Excel formula rather than a
    macro.

    Thanks.
    --
    tb

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat May 9 10:33:25 2020
    Hi,

    Am Fri, 8 May 2020 20:49:06 -0500 schrieb tb:

    I need to extract part numbers from text strings that look like this:

    Example 1:
    J72 5-40 SOLENOID VALVE (there is a double space between J72
    and SOLENOID)

    Example 2:
    SOLENOID VALVE K77 8-32 (there is a double space between VALVE
    and K77)

    Example 3:
    SOLENOID VALVE _X37 1-22 X Y Z (there is an underscore before X37
    and there are 2+ spaces between _X37 and whatever comes after that)

    The formula that I am looking for would extract the part numbers as follows: Example 1: J72 5-40
    Example 2: K77 8-32
    Example 3: X37 1-22 (i.e. w/o the underscore and w/o whatever comes
    after the 2+ spaces)

    if there is always "SOLENOID VALVE" into the string, try:

    =LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"SOLENOID VALVE",),"_",)),8)


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From tb@21:1/5 to Claus Busch on Sat May 9 08:55:47 2020
    On 05/09/2020 03:33 AM, Claus Busch wrote:
    Hi,

    Am Fri, 8 May 2020 20:49:06 -0500 schrieb tb:

    I need to extract part numbers from text strings that look like this:

    Example 1:
    J72 5-40 SOLENOID VALVE (there is a double space between J72
    and SOLENOID)

    Example 2:
    SOLENOID VALVE K77 8-32 (there is a double space between VALVE
    and K77)

    Example 3:
    SOLENOID VALVE _X37 1-22 X Y Z (there is an underscore before X37
    and there are 2+ spaces between _X37 and whatever comes after that)

    The formula that I am looking for would extract the part numbers as follows: >> Example 1: J72 5-40
    Example 2: K77 8-32
    Example 3: X37 1-22 (i.e. w/o the underscore and w/o whatever comes
    after the 2+ spaces)

    if there is always "SOLENOID VALVE" into the string, try:

    =LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"SOLENOID VALVE",),"_",)),8)


    Regards
    Claus B.

    Thanks, Claus.

    Unfortunately our parts have different descriptions...

    I think that I can quickly reformat the parts like in Example 2 to
    resemble those that are like Example 1.

    For instance: Reformat "SOLENOID VALVE K77 8-32" to become "K77 8-32
    SOLENOID VALVE". After that, I would need to be able to extract
    whatever comes before the double space.

    The problem then will be to also find a way to extract part numbers that
    are between an underscore and double --or triple or quadruple, etc.--
    space, like in Example 3.

    What do you think? If I can reformat parts like those in Example 2 to
    resemble those like in Example 1, would you then be able to help me with
    a formula that extracts part numbers similar to Example 1 and Example 3?

    Thanks.
    --
    tb

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat May 9 17:09:57 2020
    Hi again,

    Am Sat, 9 May 2020 08:55:47 -0500 schrieb tb:

    Unfortunately our parts have different descriptions...

    download the workbook from here: https://1drv.ms/x/s!AqMiGBK2qniTget2FoPBi2fW71YHlA?e=4is9jJ

    and check out if the formulas or the function will work for you.


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat May 9 16:17:48 2020
    Hi,

    Am Sat, 9 May 2020 08:55:47 -0500 schrieb tb:

    Unfortunately our parts have different descriptions...

    then try:

    =IF(ISNUMBER(--MID(A1,2,1)),LEFT(A1,FIND(" ",A1)-1),IFERROR(MID(A1,FIND(" _",A1)+2,8),MID(A1,FIND(" ",A1)+2,99)))


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From tb@21:1/5 to Claus Busch on Sat May 9 11:39:55 2020
    On 05/09/2020 10:09 AM, Claus Busch wrote:
    Hi again,

    Am Sat, 9 May 2020 08:55:47 -0500 schrieb tb:

    Unfortunately our parts have different descriptions...

    download the workbook from here: https://1drv.ms/x/s!AqMiGBK2qniTget2FoPBi2fW71YHlA?e=4is9jJ

    and check out if the formulas or the function will work for you.


    Regards
    Claus B.

    The link sends me to a page indicating that the item might not exist or
    is no longer available.
    --
    tb

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat May 9 18:56:26 2020
    Hi,

    Am Sat, 9 May 2020 11:39:55 -0500 schrieb tb:

    The link sends me to a page indicating that the item might not exist or
    is no longer available.

    for me the link works fine. Try it again.


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat May 9 19:59:07 2020
    Hi,

    Am Sat, 9 May 2020 12:53:09 -0500 schrieb tb:

    It does not seem to work for me...

    The link redirects to

    <https://onedrive.live.com/view.aspx?resid=9378AAB6121822A3!30198&ithint=file%2cxlsx&wdo=2&authkey=!ABaDwYtn1u9WB5Q>

    ...and it tells me that "This item might not exist or is no longer available".

    send me an email. I send you the workbook via email.


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From tb@21:1/5 to Claus Busch on Sat May 9 12:53:09 2020
    On 05/09/2020 11:56 AM, Claus Busch wrote:
    Hi,

    Am Sat, 9 May 2020 11:39:55 -0500 schrieb tb:

    The link sends me to a page indicating that the item might not exist or
    is no longer available.

    for me the link works fine. Try it again.


    Regards
    Claus B.

    It does not seem to work for me...

    The link redirects to

    <https://onedrive.live.com/view.aspx?resid=9378AAB6121822A3!30198&ithint=file%2cxlsx&wdo=2&authkey=!ABaDwYtn1u9WB5Q>

    ...and it tells me that "This item might not exist or is no longer
    available".
    --
    tb

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)