separation alpha numerics

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

Hi

I have the data as shown below

123456.tif
123-456-1.tif
123456-1.tif
12345.tif
123456tif
123456a.tif

I need the first 6 numbers in column B
Please help me

Thanks in advance.
 
Hi

I have the data as shown below

123456.tif
123-456-1.tif
123456-1.tif
12345.tif
123456tif
123456a.tif

I need the first 6 numbers in column B
Please help me

Thanks in advance.
Click to expand...

=left("Whatever cell you are looking at,6)*1 Soemtimes you ahve to
multiply times 1 so that it turns it into a number.
 
Try this...

=VALUE(SUBSTITUTE(MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))),"-",""))

This is an array function so after pasting the formula press F2 and give
Cntrl+Shift+Enter.

Remember to Click Yes, if this post helps!
 
Excellent, Many more thanks
Please help me for below one also.
123-46-7.tif

Is there any way to replace "-", "_", "tif" at a time in the single formula.

Thanks in advance
 
Thanks, but I need 6 numbers

jlclyde said:
=left("Whatever cell you are looking at,6)*1 Soemtimes you ahve to
multiply times 1 so that it turns it into a number.
.
Click to expand...
 
Now only I have seen your reply and thanks for feeding back.

Use the formula like the below...

=SUBSTITUTE(MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$9),1)),0),COUNT(1*MID(A2,ROW($1:$9),1))),"-","_")

This is an array function so after pasting the formula press F2 and give
Cntrl+Shift+Enter.

Remember to Click Yes, if this post helps!
 
Back
Top