Remember Me?

#1
May 9th 20, 02:49 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 84
Extraction of Part Numbers from Text Strings

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)

macro.

Thanks.
--
tb

#2
May 9th 20, 09:33 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
Extraction of Part Numbers from Text Strings

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
#3
May 9th 20, 02:55 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 84
Extraction of Part Numbers from Text Strings

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

#4
May 9th 20, 03:17 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
Extraction of Part Numbers from Text Strings

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
#5
May 9th 20, 04:09 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
Extraction of Part Numbers from Text Strings

Hi again,

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

Unfortunately our parts have different descriptions...

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

#6
May 9th 20, 05:39 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 84
Extraction of Part Numbers from Text Strings

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...

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

#7
May 9th 20, 05:56 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
Extraction of Part Numbers from Text Strings

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
#8
May 9th 20, 06:53 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 84
Extraction of Part Numbers from Text Strings

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...

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

#9
May 9th 20, 06:59 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
Extraction of Part Numbers from Text Strings

Hi,

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

It does not seem to work for me...

...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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post tb Excel Worksheet Functions 2 May 2nd 19 02:01 PM tb Excel Worksheet Functions 3 June 21st 11 04:22 PM Jack Sons Excel Discussion (Misc queries) 1 April 27th 10 10:58 PM andy from maine Excel Discussion (Misc queries) 0 March 28th 05 09:47 PM Anders Excel Programming 0 September 8th 04 11:03 PM

All times are GMT +1. The time now is 09:47 AM.