• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract a part number with letters from a statement?

A

alex_13

New Member
Hello, I need some help, I need to extract a number from a statement, but the issue here is that in some cases the part number I need to pull out contains letters & punctuations, for example:

  • 48AC90123 connector holder
  • terminal 14513-2ZZV tool
  • bearing 00907850
These type of numbers are inside a description as you can see, so my question is how can I extract the part number with the letters & punctuations that it may contain? and get the final result as the following:

  • 48AC90123
  • 14513-2ZZV
  • 00907850
 
Hi Alex ,

You need to define what constitutes a part number.

1. What will it start with ?

2. Will it have only alphabets and digits , and possibly the hyphen ?

3. What will it end with ?

Lastly how many such part numbers will occur in each string / cell ?

Narayan
 
Hi Alex,

Here is a wild guess (see attached file). I used two named ranges, let me/us know if you need help with that.
 

Attachments

  • ExampleD.xlsx
    10.5 KB · Views: 20
Hello,
Here is one more approach (for a string in A2)
=LOOKUP(LOOKUP(LEN($A2),FIND(ROW($1:$10)-1,$A2)), FIND(" ", " "&TRIM(A2),ROW(OFFSET(A$1,,,LEN($A2)))), MID($A2, ROW(OFFSET(A$1,,,LEN($A2))), MMULT(FIND(" ",{""," "}&TRIM($A2)&" ", ROW(OFFSET(A$1,,,LEN($A2)))),{1;-1})))

Cheers,
Sajan.
 
Hi Xiq,
Which character is that? I do not see any reference to any such characters in the OP's post. What am I overlooking?
 
Character 160 is non-breaking space which looks pretty much the same as white space we normally use but isn't the same character [Char 32]. It can come in the data if it is being copied from html source. There could be some softwares which export data with nbsp.

It doesn't sound nasty, does it? It causes problems if you intend to trim data as the normal TRIM formula won't work with it.

And from the looks of it, I'd guess Xiq is referring to blue marked part where it won't find the white space formula is looking for.
=LOOKUP(LOOKUP(LEN($A2),FIND(ROW($1:$10)-1,$A2)), FIND(" ", " "&TRIM(A2),ROW(OFFSET(A$1,,,LEN($A2)))), MID($A2, ROW(OFFSET(A$1,,,LEN($A2))), MMULT(FIND(" ",{""," "}&TRIM($A2)&" ", ROW(OFFSET(A$1,,,LEN($A2)))),{1;-1})))
 
Where are you guys finding the non-breaking space? Since the OP did not post a workbook, I looked in the file that Xiq uploaded, and found only regular spaces in the strings.

Your comments reminded me of the movie "Sixth Sense" where the little boy said "I see dead people"... in your case, it seems to be "I see non-breaking space characters"! :p
 
Hi Sajan,

I found the non-breaking space in between "14513-2ZZV" and "tool". See attached file for comparison of the results :)
 

Attachments

  • ExampleD.xlsx
    10.7 KB · Views: 8
Hi Xiq,
Very interesting... It must be how I copy/paste text from posts... I paste as text, and do not get these special characters.

-Sajan.
 
I tried this with IE 8 and Chrome version 31, and did not get any special characters. It could be because I copy and paste as text.
 
@shrivallabha
Hi!
Firefox 25.0.1, Aurora 27.0.a2, Nightly 28.0.a1, Chrome 30.0.1599.101 m, IE 11.0.9600.16428 (sorry, this one just for testing, I swear I don't use it!)... and same as Sajan.
Regards!

so...

@Xiq
Hi!
Running Excel under Linux? :eek:
Regards!
 
I did not get the darn character either but I was curious as to what was causing it. What I had posted above was clarification on Xiq's Char160 comment.

I use Firefox 25.0. And the results were the same as yours and Sajan's.

Direct copy and paste as it is doesn't change anything for me. Copy and Paste as text option would of course remove formatting but the underlying character set would still be the same. NBSP is there in both Text or Unicode Text. See character 160 here:
http://www.alanwood.net/demos/ansi.html

I asked Sajan's browser settings just to be sure. SirJB7 has checked it with almost all of them. So unless Xiq tells his browser I'd still be confused :confused:
 
Hi, Xiq!
Two of us won't be able to sleep until you tell us how did you get that f.....g CHAR(160), so come here asap and tell us. I'll promise to read it after my Saturday afternoon's siesta.
Regards!
PS: f.....g stands for... fooling, of course! :rolleyes:
 
Following should work as well:
Code: 
=TRIM(MID(SUBSTITUTE(A1&" 0123456789"," ",REPT(" ",99)),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1&" 0123456789"," ",REPT(" ",99)))),99))
 
Hi Shrivallabha,
I interpreted the OP's question to mean the following results:
abc123-qw One Two --> abc123-qw
One 0123213 Two --> 0123213
One Two abc123-qw123 --> abc123-qw123

Regards,
Sajan.
 
Sajan said:
Hi Shrivallabha,
I interpreted the OP's question to mean the following results:
abc123-qw One Two --> abc123-qw
One 0123213 Two --> 0123213
One Two abc123-qw123 --> abc123-qw123

Regards,
Sajan.
Click to expand...
Agree. The formula I have posted works with OP's data [and it will not work where the numeric data is prefixed with letters as you've shown].

The formula then can be adjusted but that is up to OP to clarify if it is sufficient or not.

"Assuming too much and qualifying too much are two faces of the same problem" says Aladin Akyurek's signature on MrExcel.
 
Understood. Thanks for the clarification.

Indeed, I always try to provide solutions based on my interpretation of the requirements, unless the OP was explicit and clear about what s/he wanted. In this case, the OP said "the part number I need to pull out contains letters & punctuations" but did not specify whether they were at the beginning, in the middle, end, etc.". So I let my imagination fill in the blanks! (An example does not suggest that what was provided was comprehensive.)

In any case, the readers can use whatever they want... I doubt the OP is coming back!

Cheers,
Sajan.
 
:eek:
Can it really be, because I did not copy & paste as value/text??? That would make things so much easier :rolleyes:

I don't understand why it would place that damn character on that particular spot though...

FYI:
Chrome
Excel 2010
 
You must log in or register to reply here.
Back
Top