question

ronbarlow-5250 avatar image
0 Votes"
ronbarlow-5250 asked emilyhua-msft commented

Extract string from another string

Hello

I've generated a list of files in a folder ...

-a---- 02/08/2021 16:18 32624 My File1.xlsx
-a---- 02/08/2021 16:19 19515 File2.xlsx

I need to return the file name excluding the file extension, e.g. My File1, File2

The file name starts at character 50, but can include spaces. I've tried various methods see below,
but can't get it quite right

=LEFT(RIGHT(A1, LEN(A1) -MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),1)=" "))), LEN(RIGHT(A1, LEN(A1) -MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),1)=" "))))-5)

=LEFT((MID(A1,50,LEN(A1))),FIND(".",MID(A1,50,LEN(A1))-1))

Any pointers much appreciated

Thanks in advance
Ron

office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

@ronbarlow-5250

The file name starts at character 50, but can include spaces ```

Let me see if I understand correctly, for the first sample "-a---- 02/08/2021 16:18 32624 My File1.xlsx", the first letter "M" of the file name is the 50th character in the string; for the second sample "-a---- 02/08/2021 16:19 19515 File2.xlsx", the first letter "F” of the file name is the 50th character in the string.

If yes, please try following formula.
=MID(A1,50,FIND(".",A1,50)-50)
120636-image.png

If not, please feel free to let me know the misunderstanding.


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


image.png (11.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ronbarlow-5250
If you have a chance back, please check whether our information is helpful.
Any issues, please feel free to post back.

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered

Hi @ronbarlow-5250

In case you would have strings like below

120674-demo.png

With Excel 365 that you seem to have

 =LET(
     fullFileName,   RIGHT(A1,LEN(A1)-50),
     seqLength,      SEQUENCE(LEN(fullFileName)),
     LEFT(fullFileName,
         LOOKUP(2, 1/(MID(fullFileName,seqLength,1)="."), seqLength)
         -1
     )
 )


demo.png (30.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.