separation alpha numerics

[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.

 

[jlclyde]

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.

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

 

[Ms-Exl-Learner]

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!

 

[Rod]

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

 

[Rod]

Thanks, but I need 6 numbers

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

 

[Ms-Exl-Learner]

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!