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