I have copied hundreds of .jpg file names, using DOS, to copy them into a
listing within a worksheet. As a substantial amount are numeric with a file
extension I would like to split the cells to remove file extension to give me
a numeric result.

i.e.
"123.jpg" into a numeric "123" or "Many files.jpg" into "Many files"

The file names are variable in size and are either numeric or just characters
Thank you

To remove the last 4 characters within a cell:

1. Select the cell(s) that you want to remove the last 4 characters from.
2. In the formula bar at the top of the screen, type
   Code:

   =LEFT(A1,LEN(A1)-4)

   where A1 is the cell reference of the first cell you selected.
3. Press Enter on your keyboard.

If you want to remove the file extension from the cell(s) as well:

1. Select the cell(s) that you want to remove the file extension from.
2. In the formula bar at the top of the screen, type
   Code:

   =LEFT(A1,FIND(".",A1)-1)

   where A1 is the cell reference of the first cell you selected.
3. Press Enter on your keyboard.

Try

=LEFT(A1,LEN(A1)-4)

Mike

"Problem poser" wrote:

I have copied hundreds of .jpg file names, using DOS, to copy them into a
listing within a worksheet. As a substantial amount are numeric with a file
extension I would like to split the cells to remove file extension to give me
a numeric result.

i.e.
"123.jpg" into a numeric "123" or "Many files.jpg" into "Many files"

The file names are variable in size and are either numeric or just characters
Thank you

try this

=SUBSTITUTE(A1,".jpg","")

or

Select the column
Go to Data | text column | delimited | next | choose other option :
put an dot | next
select the jpg column in data preview | choose do not import (skip)
option | finish |


On Jan 17, 12:10*am, Problem poser <Problem
wrote:
I have copied hundreds of .jpg file names, using DOS, to copy them into a
listing within a worksheet. As a substantial amount are numeric with a file
extension I would like to split the cells to remove file extension to give me
a numeric result.

i.e.
"123.jpg" into a numeric "123" or "Many files.jpg" into "Many files"

The file names are variable in size and are either numeric or just characters
Thank you

Since there are different lengths, I would use Ctrl+H
replace: .jpg
with: (leave this blank)
Replace All



"Problem poser" wrote:

I have copied hundreds of .jpg file names, using DOS, to copy them into a
listing within a worksheet. As a substantial amount are numeric with a file
extension I would like to split the cells to remove file extension to give me
a numeric result.

i.e.
"123.jpg" into a numeric "123" or "Many files.jpg" into "Many files"

The file names are variable in size and are either numeric or just characters
Thank you

Thank you Angela, Mike and Muddan
This is the first time that I have used this discussion group
All answers work and "I am impressed"with the time to respond.

Thank you again

Michael