separation alpha numerics
 

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.

separation alpha numerics
 

On Jan 20, 11:08*am, Rod wrote:
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.

separation alpha numerics
 

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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.

separation alpha numerics
 

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

"Ms-Exl-Learner" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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.

separation alpha numerics
 

Thanks, but I need 6 numbers

"jlclyde" wrote:

On Jan 20, 11:08 am, Rod wrote:
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.
.

separation alpha numerics
 

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,RO W($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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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

"Ms-Exl-Learner" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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.