| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort alpha neumeric fields that have an alpha suffix? | Excel Worksheet Functions | |||
| change rows and colums to display alpha instead of numerics | Excel Discussion (Misc queries) | |||
| Extract Numerics only | Excel Discussion (Misc queries) | |||
| A smart way to parse alpha numerics? | Excel Discussion (Misc queries) | |||
| The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Linear Mode
