having this data in excel cell
kerass(xcvbn=2, abcdefg_iD='510863005')
having 100000 of records so, how to extract id number using excel formula from that excel sheet
Thanks in advance
Add a comment
|
3 Answers
Assuming your value is in cell A2. Here is a formula to be inserted
=SUBSTITUTE(RIGHT(A2,LEN(A2)-SEARCH("_iD='",A2)-4),"')","")
This means:
- Search for "_iD='"
- Once found, subtract the length of "_iD='" of that location (hence the 4)
- Use the total length of the string, subtracted with location and 4, in order to find where the actual identifier begins
- Take the right part of the string, only the part behind "_id='...'" remains
- In that part, replace "')" by an empty string (otherwise you get "...005')"
Comments
Alternative answer:
=MID(A1,FIND("'",A1)+1,9)
The find formula finds the first "'" starting position
Assuming all the ID's are 9 digits, the MID formula uses the starting pos. from find and takes the following 9 digits.
Comments
So assuming your text is in cell A2, and that the id is the first 9 characters of the last 11 then this is the simplest I can think of:
=LEFT(RIGHT(A2,11),9)
But if extra characters get added or the ID changes length.
Does have the benefit of being minimalist on functions and low volativity.
