Seperate alphanumeric text from a line text in excel

[Dipti Sajit]

Hi Friends
Am stuck with an issue here. i have a line text in excel something like below
PHOENIX DRAW B14-119026-0 PSNO 33

i want to extract numbers ahead of word DRAW and PSNO in entire sheet into different columns. Also issue is at few place the placement of word DRAW and PSNO is not at same position. Further DRAW word is also spelt as DRAWING at few places. Two more examples as below

3/2-ABDGEVJDHIHD>HKSHK372 352 222 0 / 894 101 610 2 Drawing Number: 055 7611-5/84,86,88,90,12 7,140
ANSHSHUGDDHDLKJHDHHKCNKNKJJ KSLKJS:JS:O JKOJDIODOI:IDH B&W TYPE 7K90MC-C DRAW 90910-128 PSNO 950

CAN someone please help a quick formula to tackle this. Using mid and Search functions i have brought my list down to 11k rows out of 45k+ which has only this two words i.e. DRAW and STRING. STUCK here though

[FDibbins]

Hi, welcome to the forum

So that is all in 1 cell?
What would your result look like?

[Dipti Sajit]

Hi Thanks for the warm welcome.
Yes this all text falls in one cell
The result needs to be as below in two different columns

Drawing Number PSNO
5472-0800-0003 160

[JohnTopley]

..and result from

3/2-ABDGEVJDHIHD>HKSHK372 352 222 0 / 894 101 610 2 Drawing Number: 055 7611-5/84,86,88,90,12 7,140 ???

[Dipti Sajit]

It wud be just the number ahead of word drawing number i,e, 055 7611-5

I know the text has lot of inconsistency hence struggling.

[JohnTopley]

Try

Drawing Assumes format as your example with 3 "/" in string .....

=IFERROR(TRIM(MID(A1,FIND("Drawing Number:",A1,1)+15,FIND("?",SUBSTITUTE(A1,"/","?",3),1)-FIND("Drawing Number:",A1,1)-15)),MID(A1,FIND("DRAW",A1)+5,FIND("PSNO",A1,1)-FIND("DRAW",A1)-5))

PSNO

=IFERROR(MID(A2,FIND("PSNO",A2,1)+4,255),"")

[Dipti Sajit]

Hi John
This is awesome. But still just at few places am getting #VALUE as error. I feel the len of the line is an issue in such places. wherever the line text is crossing 80 characters its throwing an error and there are close to 3000 such examples

[Dipti Sajit]

i can add an example JOHNTOPLEY to help u see
1. 3/2-WAY VALVE HDGGDD 5S50MC MK 6 Drawing Number: 90703-0120/130 Serial Number: #VALUE
on the other hand it works on below statement. LENGTH of the line is not an issue
5/2 WAY MANUAL VALVE DRAWING NO: 1257140-9 MBD NO: 677086 MAKE DKKDHD TYPE 466 201 000 DRAW 021-119047-2 PSNO 60

[JohnTopley]

Try

=IFERROR(TRIM(MID(A1,FIND("Drawing Number:",A1,1)+15,FIND("?",SUBSTITUTE(A1,"/","?",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))),1)-FIND("Drawing Number:",A1,1)-15)),MID(A1,FIND("DRAW",A1)+5,FIND("PSNO",A1,1)-FIND("DRAW",A1)-5))

[Dipti Sajit]

NO success am really stuck with this 8000 lines resolved 3000 to go
"200/201 TOOL FOR FUEL INJECTION PUMP MAKE STX MAN B&W TYPE L32/40 DRAW 130500505A" Not working on this ones throwing error #VALUE

[Dipti Sajit]

THE VALUE error seems to be in cells where the output is in ALPHA NUMERIC FORMAT.. thats what i assume

[JohnTopley]

Try

=IFERROR(TRIM(MID(A3,FIND("Drawing Number:",A3,1)+15,FIND("?",SUBSTITUTE(A3,"/","?",LEN(A3)-LEN(SUBSTITUTE(A3,"/",""))),1)-FIND("Drawing Number:",A3,1)-15)),IFERROR(MID(A3,FIND("DRAW",A3)+5,FIND("PSNO",A3,1)-FIND("DRAW",A3)-5),MID(A3,FIND("DRAW",A3)+5,255)))

The reason it failed was because there was no PSNO.

We could spend all day looking at all the variants: you will have to accept that you are very likely to continue getting errors.

Even using VBA we will need ALL the possible variants.

Excel is not designed to handle "fuzzy" logic!

[jason.b75]

Another one to try.

=TRIM(IF(ISNUMBER(SEARCH("draw ",A1)),MID(SUBSTITUTE(SUBSTITUTE(A1,"DRAW "," PSNO")," PSNO",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)),RIGHT(SUBSTITUTE(LEFT(A1,SEARCH(" Drawing",A1)),"/",REPT(" ",LEN(A1))),LEN(A1))))

If you post any more examples where our suggestions don't work, please provide the expected result for those examples as well. Not all are obvious and it's too close to the weekend to attempt mind reading.