Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 1st 19, 05:40 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 80
Default Capturing Part Numbers Within Text Strings

I need to capture part numbers that are embedded in text strings.

For instance, the string in A1 is:

9UNI01 05A4462-300202 US1U US1C Standard

....and I am looking for a function (in B1) that will capture the part
number, i.e.:

05A4462-300202

The part numbers are always in the second position of a string and
separated --right and left of them-- by several blank characters.

Thanks.
--
tb

  #2   Report Post  
Old May 1st 19, 06:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,696
Default Capturing Part Numbers Within Text Strings

Hi,

Am Wed, 1 May 2019 16:40:28 +0000 (UTC) schrieb tb:

I need to capture part numbers that are embedded in text strings.

For instance, the string in A1 is:

9UNI01 05A4462-300202 US1U US1C Standard

...and I am looking for a function (in B1) that will capture the part
number, i.e.:

05A4462-300202

The part numbers are always in the second position of a string and
separated --right and left of them-- by several blank characters.


try:

=LEFT(TRIM(MID(A1,FIND(" ",A1),99)),FIND(" ",TRIM(MID(A1,FIND(" ",A1),99))))


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old May 2nd 19, 02:01 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 80
Default Capturing Part Numbers Within Text Strings

On 5/1/2019 at 12:04:29 PM Claus Busch wrote:

Hi,

Am Wed, 1 May 2019 16:40:28 +0000 (UTC) schrieb tb:

I need to capture part numbers that are embedded in text strings.

For instance, the string in A1 is:

9UNI01 05A4462-300202 US1U US1C Standard

...and I am looking for a function (in B1) that will capture the
part number, i.e.:

05A4462-300202

The part numbers are always in the second position of a string and
separated --right and left of them-- by several blank characters.


try:

=LEFT(TRIM(MID(A1,FIND(" ",A1),99)),FIND(" ",TRIM(MID(A1,FIND("
",A1),99))))


Regards
Claus B.


Thanks, Claus!

--
tb


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
Help With Searching For Strings In Part Numbers tb Excel Worksheet Functions 3 June 21st 11 04:22 PM
Capturing a word sequence as part of a text in a spreadsheet cell T.Mad Excel Worksheet Functions 4 July 3rd 07 12:08 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
ADO inserts text strings not numbers when table is empty Rob Nicholson Excel Programming 5 March 5th 04 06:10 PM


All times are GMT +1. The time now is 04:55 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017