Extract mixed of text numbers characters

[sotor]

I tried to extract mixed text & numbers using formula =MID(LEFT(A1,FIND("T",A1)+9),FIND("T",A1)+0,LEN(A1))
I also tried extending it for other mixed characters but not having much luck.

Anyone have a solution for the data below?

[TABLE="width: 495"]
<tbody>[TR]
[TD]Detail
[/TD]
[TD]Result needed
[/TD]
[/TR]
[TR]
[TD]F-001\017548942\T048321/EM/41/KC/TB/jn\
[/TD]
[TD]T048321EM
[/TD]
[/TR]
[TR]
[TD]D-001\4535623-0001\D100492EM
[/TD]
[TD]D100492EM
[/TD]
[/TR]
[TR]
[TD]D-041\012258188\D100035EM--17/REN\001361430-0017
[/TD]
[TD]D100035EM
[/TD]
[/TR]
[TR]
[TD]F-005\002913831\KC/TB/B.208.0444\016300576\BT
[/TD]
[TD]B.208.0444
[/TD]
[/TR]
[TR]
[TD]F-005\003049918\AMSF/RJP/F.043.0501\017604273\Fi
[/TD]
[TD]F.043.0501
[/TD]
[/TR]
[TR]
[TD]F-012\006564975\AAR.24-5\AMER
[/TD]
[TD]AAR.24-5
[/TD]
[/TR]
[TR]
[TD]F-005\003055137\KAE/SHF/K.024.0013\017872828\V
[/TD]
[TD]K.024.0013
[/TD]
[/TR]
[TR]
[TD]F-005\003056146\M.167.0289\017825084\m
[/TD]
[TD]M.167.0289
[/TD]
[/TR]
[TR]
[TD]F-116\000751537\MAL/CT25330\Tel
[/TD]
[TD]CT25330
[/TD]
[/TR]
[TR]
[TD]F-020\014739422\O046865/EM/35/JML\
[/TD]
[TD]O046865EM
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

I don't see a logical patern to put in formula and I can't even guess the part(s) that you want. Can you explain something like

if start with F and 3rd element is 7 caracter, add 4th element
or if find EM, take 8 caracters before (without /)
if element with dots, take that one
...

 

[sotor]

I don't see a logical patern to put in formula and I can't even guess the part(s) that you want. Can you explain something like

if start with F and 3rd element is 7 caracter, add 4th element
or if find EM, take 8 caracters before (without /)
if element with dots, take that one
...

Tricky one.. the result needed, letters that start with F,B, K M T, D,O are 9 characters without / there are a few characters like CT25330 & AAR.24-5 that are less than 9, (7 & 8 characters).

If there is a logical solution for 9 characters I can live with that (I can do without 7 and 8 characters).

 

[sotor]

Tricky one.. the result needed, letters that start with F,B, K M T, D,O are 9 characters without / there are a few characters like CT25330 & AAR.24-5 that are less than 9, (7 & 8 characters).

If there is a logical solution for 9 characters I can live with that (I can do without 7 and 8 characters).

Can anyone else suggest solution for the above?

 

[jtakw]

Hi,

This formula will extract what you want for your given samples.
If you have more "different" data formats, I may or may not be able to help further, see how this works for you.

Book1
	A	B
1	Detail
2	F-001\017548942\T048321/EM/41/KC/TB/jn\	T048321EM
3	D-001\4535623-0001\D100492EM	D100492EM
4	D-041\012258188\D100035EM--17/REN\001361430-0017	D100035EM
5	F-005\002913831\KC/TB/B.208.0444\016300576\BT	B.208.0444
6	F-005\003049918\AMSF/RJP/F.043.0501\017604273\Fi	F.043.0501
7	F-012\006564975\AAR.24-5\AMER	AAR.24-5
8	F-005\003055137\KAE/SHF/K.024.0013\017872828\V	K.024.0013
9	F-005\003056146\M.167.0289\017825084\m	M.167.0289
10	F-116\000751537\MAL/CT25330\Tel	CT25330
11	F-020\014739422\O046865/EM/35/JML\	O046865EM
Sheet349

Cell Formulas
Range	Formula
B2	=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""),IFERROR(FIND("EM",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""))+1,FIND("",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""))-1)),"/",REPT(" ",99)),99))

Formula copied down.

 

[sotor]

Hi,

This formula will extract what you want for your given samples.
If you have more "different" data formats, I may or may not be able to help further, see how this works for you.

	A	B
Detail
F-001\017548942\T048321/EM/41/KC/TB/jn\	T048321EM
D-001\4535623-0001\D100492EM	D100492EM
D-041\012258188\D100035EM--17/REN\001361430-0017	D100035EM
F-005\002913831\KC/TB/B.208.0444\016300576\BT	B.208.0444
F-005\003049918\AMSF/RJP/F.043.0501\017604273\Fi	F.043.0501
F-012\006564975\AAR.24-5\AMER	AAR.24-5
F-005\003055137\KAE/SHF/K.024.0013\017872828\V	K.024.0013
F-005\003056146\M.167.0289\017825084\m	M.167.0289
F-116\000751537\MAL/CT25330\Tel	CT25330
F-020\014739422\O046865/EM/35/JML\	O046865EM

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>

Sheet349​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""),IFERROR(FIND("EM",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""))+1,FIND("",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""))-1)),"/",REPT(" ",99)),99))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

Wow thank you, I copied the formula across but seem to get #value ! I tried to evaluate the formula but no luck. Would it be possible to send an excel file or resend the formula?

 

[jtakw]

Wow thank you, I copied the formula across but seem to get #value ! I tried to evaluate the formula but no luck. Would it be possible to send an excel file or resend the formula?

What do you mean by "Across"? As in your OP and my sample in Post # 5, Formula Copied Down Column, Not across Row.

 

[sotor]

What do you mean by "Across"? As in your OP and my sample in Post # 5, Formula Copied Down Column, Not across Row.

Sorry my typo meant copied down.

Detail
F-001\017548942\T048321/EM/41/KC/TB/jn\	=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""),IFERROR(FIND("EM",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""))+1,FIND("",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"","|",2))+1,99),"/EM","EM"),"--",""))-1)),"/",REPT(" ",99)),99))
D-001\4535623-0001\D100492EM	#VALUE!

<tbody>
</tbody>

 

[jtakw]

Don't know what your problem is, Works for me...

Book1
	A	B
12	F-001\017548942\T048321/EM/41/KC/TB/jn\	T048321EM
13	D-001\4535623-0001\D100492EM	D100492EM
Sheet349

Cell Formulas
Range	Formula
B12	=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A12,FIND("|",SUBSTITUTE(A12,"","|",2))+1,99),"/EM","EM"),"--",""),IFERROR(FIND("EM",SUBSTITUTE(SUBSTITUTE(MID(A12,FIND("|",SUBSTITUTE(A12,"","|",2))+1,99),"/EM","EM"),"--",""))+1,FIND("",SUBSTITUTE(SUBSTITUTE(MID(A12,FIND("|",SUBSTITUTE(A12,"","|",2))+1,99),"/EM","EM"),"--",""))-1)),"/",REPT(" ",99)),99))

 

[jtakw]

I know what the problem is, the forum is NOT showing the formula correctly with MrExcelHTMLmaker.

Copy this formula:

=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"\","|",2))+1,99),"/EM","EM"),"--","\"),IFERROR(FIND("EM",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"\","|",2))+1,99),"/EM","EM"),"--","\"))+1,FIND("\",SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"\","|",2))+1,99),"/EM","EM"),"--","\"))-1)),"/",REPT(" ",99)),99))