Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 9th 20, 02:49 AM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 84
Default 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)

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

Thanks.
--
tb

  #2   Report Post  
Old 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
Default 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   Report Post  
Old May 9th 20, 02:55 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 84
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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...


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


  #6   Report Post  
Old May 9th 20, 05:39 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 84
Default 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...


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

  #7   Report Post  
Old 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
Default 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   Report Post  
Old May 9th 20, 06:53 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 84
Default 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...

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

  #9   Report Post  
Old 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
Default 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...

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Capturing Part Numbers Within Text Strings tb Excel Worksheet Functions 2 May 2nd 19 02:01 PM
Help With Searching For Strings In Part Numbers tb Excel Worksheet Functions 3 June 21st 11 04:22 PM
VBA extraction of a part of the cells content Jack Sons Excel Discussion (Misc queries) 1 April 27th 10 10:58 PM
2 more questions about extracting numbers from text strings andy from maine Excel Discussion (Misc queries) 0 March 28th 05 09:47 PM
Text strings recognized as numbers given different regional settin Anders Excel Programming 0 September 8th 04 11:03 PM


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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017