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

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

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

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

Hi again,

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

Unfortunately our parts have different descriptions...

download the workbook from he
https://1drv.ms/x/s!AqMiGBK2qniTget2...1YHlA?e=4is9jJ

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


Regards
Claus B.
--
Windows10
Office 2016

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 he
https://1drv.ms/x/s!AqMiGBK2qniTget2...1YHlA?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

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

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

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