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))

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

<tbody>
</tbody>

 

[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