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

[István Hirsch]

Similar to the solution above (#19), but it extracts strings starting with letters too, besides, leaves the cell blank if there is nothing to extract.


=TRIM(MID(SUBSTITUTE(" "&A1&" "," ",REPT(" ",99)),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(" "&A1&" 0123456789"," ",REPT(" ",99))))-99,198))

 

[shrivallabha]

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.

Re-reading that part makes me think that your interpretation really covers most of the bases and Istvan Hirsch's formula covers those bases [I had something similar in my mind if the need arose]. It is an interesting way of dealing with MIN function.

I do not like conjectures / interpretations especially if someone is having real life data. The data posted should represent the situation as completely as possible.

I do hope OP comes back and has a say but I personally "interpret" no feedback as generally good feedback

 

[SirJB7]

@all
Hi!
So then there wasn't any actually f.....g Char(160) anywhere... but in Xiq's post
alex_13 is innocent, guillotine for Xiq!!!
Regards!

 

[SirJB7]

I do hope OP comes back and has a say but I personally "interpret" no feedback as generally good feedback

@shrivallabha
Hi!
I agree. Silence or lack of further answers/comments are always to be taken positively.
Well, almost always, I'd say 99.99%. Wanna give a look to one of the 0.01% exceptions? Go to The Lounge, find the sticky posts, discard that of Introduce Yourself, discard that of Ninjas, and among the remaining ones you'll surely find it.
Regards!

 

[Xiq]

guillotine for Xiq!!!
Regards!

Please have mercy!!


Btw, forum update??

 

[SirJB7]

@Xiq
Hi!
1) Ok, mercy, but just this time. Hangman! Clean and sharpen the guillotine.
2) Yes, another anti-spam battle.
Regards!