Copy all text in a string prior to the last period...

A

Andrew Bell

Hello,

I am trying to gather all text before the last period in strings of text, for example I want to take everything before the file extension below, and place that in a new cell. Any thoughts how I can accomplish this? I appreciate the help. Thanks.


x:\TRX0000070.ATT%0000001.pdf
x:\TRX0000070.ATT%0000002.xlsx
x:\TRX0000070.ATT%0000003.pdf
x:\TRX0000070.ATT%0000004.pdf
 
R

Ron Rosenfeld

Hello,

I am trying to gather all text before the last period in strings of text, for example I want to take everything before the file extension below, and place that in a new cell. Any thoughts how I can accomplish this? I appreciate the help. Thanks.


x:\TRX0000070.ATT%0000001.pdf
x:\TRX0000070.ATT%0000002.xlsx
x:\TRX0000070.ATT%0000003.pdf
x:\TRX0000070.ATT%0000004.pdf

A1: Data
B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)
 
R

Rick Rothstein

I am trying to gather all text before the last period in
strings of text, for example I want to take everything
before the file extension below, and place that in a
new cell. Any thoughts how I can accomplish this? I
appreciate the help. Thanks.


x:\TRX0000070.ATT%0000001.pdf
x:\TRX0000070.ATT%0000002.xlsx
x:\TRX0000070.ATT%0000003.pdf
x:\TRX0000070.ATT%0000004.pdf

If your files are all named with the same structure that you showed us in
your sample names, then you can simply do this...

=LEFT(A1,25)

Rick Rothstein (MVP - Excel)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top