Simple String question

[ncoatney]

I'm currently using a barcode scanner to input values into an excel sheet. the barcode contains values such as:

C000111254|HPSSF2517|14NIZE
C000098567|HSCF21|22BRSS

etc.

The beginning and end string length will always be the same. 10 characters and 6. the middle set will vary in length.

I need to extract and place as follows:
A1 B1 C1
C00011254 HPSSF2517 14NIZE
C00009856 HSCF21 22BRSS

 

[gaz_chops]

If they are always separated by | then you could simply use Text to Columns with | as the separator.

 

[Fluff]

You can use Text to columns for that, select delimited & then use Other with |

 

[salim hasan]

supposing your data in the cell A1

Try those fomulas in b1,c1,and d1 respectively

=LEFT(A1,FIND("|",A1)-1)
=MID(A1,(FIND("|",(SUBSTITUTE(A1,"|","*",2)))+1),(FIND("*",(SUBSTITUTE(A1,"|","*",2)))+1)-(FIND("|",(SUBSTITUTE(A1,"|","*",2)))+1)-1)
=MID(A1,(FIND("*",(SUBSTITUTE(A1,"|","*",2)))+1),LEN(A1))

[TABLE="width: 697"]
<tbody>[TR]
[TD]C000111254|HPSSF2517|14NIZE[/TD]
[TD]C000111254[/TD]
[TD]HPSSF2517[/TD]
[TD]14NIZE[/TD]
[/TR]
[TR]
[TD]C000098567|HSCF21|22BRSS[/TD]
[TD]C000098567[/TD]
[TD]HSCF21[/TD]
[TD]22BRSS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Or with just one formula

Book1
	A	B	C	D
2	C000111254|HPSSF2517|14NIZE	C000111254	HPSSF2517	14NIZE
3	C000098567|HSCF21|22BRSS	C000098567	HSCF21	22BRSS
Totals

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",100)),(COLUMN(A1)-1)*100+1,100))

Drag down & accross