how to combine left, right or mid function

[Forestq]

Hi,

how can I read/select the number from the string as below:

for example:
A1: = "Audi A6, Poland - N123323 - SG"
A2: = "VW Polo, Denmark - (X) - N233217 CN"
A3: = "Ford Fiesta, Italy, XZ, (new) - N876233 - WW"

I wan to have only:
B1 = 123323
B2 = 233217
B3 = 876233

 

[Andrew Poulsom]

Try:

=MID(A1,FIND("- N",A1)+3,6)+0

or:

=-LOOKUP(1,-LEFT(MID(MID(A1,FIND(",",A1)+1,255),MIN(FIND(0,SUBSTITUTE(MID(A1,FIND(",",A1)+1,255),{1,2,3,4,5,6,7,8,9},0)&0)),255),ROW($1:$99)))

 

[Forestq]

thanks!

but if I have something like:

"Audi Poland -N16526-SG" or "VW Germany - N88822"

how to modify your code?

 

[Andrew Poulsom]

There's always a but ... Please post some data that shows all the permutations.

 

[TheCman81]

How about using something like this:

=TRIM(MID(SUBSTITUTE(A1,"-"," "),FIND(" N",SUBSTITUTE(A1,"-"," "))+2,6))+0

or

This one is an array formula which requires you to use Ctrl, Shift Enter

=MID(REPLACE(A1,1,FIND(" -",A1),""),MATCH(TRUE,ISNUMBER(1*MID(REPLACE(A1,1,FIND(" -",A1),""),ROW($1:$99),1)),0),COUNT(1*MID(A1,ROW($1:$99),1)))+0

 

[Forestq]

when I used code:

=TRIM(MID(SUBSTITUTE(A27,"-"," "),FIND(" N",SUBSTITUTE(A27,"-"," "))+2,6))+0

I get error on strings:

GTA-DC & NAB-MNS Tran. & SEC-N19716-SG
FW COO182-N19716-SG
Group Audi-2343-N19766-SG
Area Of ShPC -N19600-SG
[TABLE="width: 264"]
<TBODY>[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[Aladin Akyurek]

when I used code:

=TRIM(MID(SUBSTITUTE(A27,"-"," "),FIND(" N",SUBSTITUTE(A27,"-"," "))+2,6))+0

I get error on strings:

GTA-DC & NAB-MNS Tran. & SEC-N19716-SG
FW COO182-N19716-SG
Group Audi-2343-N19766-SG
Area Of ShPC -N19600-SG
[TABLE="width: 264"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Anther shot...

=LOOKUP(9.99999999999999E+307,
  --(LEFT(REPLACE(SUBSTITUTE(A1," ",""),1,1+LOOKUP(9.99999999999999E+307,
  FIND("-N",SUBSTITUTE(A1," ",""))),""),ROW(INDIRECT("1:16")))))

which covers the examples up to now.