Get characters of string

[peetman]

Hi all,

I'd kindly ask you to help with this.
I have the following string and I want to use a formula to extract only the L63004

C:\VISION Projects\MyATIrecorders\2328_14022013_1800_L63004_NEDC_cst.rec

The string will change it's format based on the user but I want always to extract a letter and the following 5 numbers. It could be M59102 or E21953, etc. The good thing is that the letter and the following 5 numbers are always separated from the rest of the string by _

Thank you,
Pedro

[TMS]

Are there always the same number of underscores?


Regards, TMS

[Special-K]

Based on TMS's method

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),3))+1,6)

assuming there are no underscores in the pathname

[TMS]

My method? I only asked a question I do like the choice of CHAR(1) as the replacement as it's highly unlikely to be found in the data

But yes, I'd have done something similar. Maybe also changed the next underscore as the chances are the next question will be, what if there are more or less characters.

Cheers, TMS

[Special-K]

I didnt not want to give you credit :-)
Not sure where I found this solution so the CHAR(1) is not my idea.

Good idea about the next underscore!

[peetman]

The underscores are always in there, but its number is not always the same. The characters I want are always in between underscores though.

[peetman]

Based on TMS's method

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),3))+1,6)

assuming there are no underscores in the pathname

It worked like a charm
Thank you very much

[Special-K]

It worked like a charm
Thank you very much

He means you too TMS :-)

[TMS]

He means you too TMS :-)

Just so I can feel that I can take some credit ...

Please Login or Register  to view this content.

Regards, TMS




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[peetman]

Hello again,
So I changed my file path and the code is retrieving the wrong answer. What I would like on this case is K57239
T:\2456_K57239_2421_05_07.rec

Could you please help to make the formulas above more robust.

Thanks in advance,
Pedro

[István Hirsch]

Try this formula (confirm with Ctrl-Shift-Enter):

=MID(A1,MATCH(1,(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)>="A")*ISNUMBER(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))+1,5)),0),6)

[peetman]

Try this formula (confirm with Ctrl-Shift-Enter):

=MID(A1,MATCH(1,(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)>="A")*ISNUMBER(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))+1,5)),0),6)

It is working
Thank you very much.