How To Extract Name & E-Code from the text string when the text string have like [e.g John(02457)]
Here you can use the below formula first extract the "Name" then extract the "E-Code" from the data table "Name & E-Code"
Formula To Extract Name
=LEFT(A2,FIND("(",A2)-1)
And the result will display John
Keep the same above formula & you can change the "(" to "{" , "[", "#" or "@"
How This Formula Works ?
(i) =LEFT(A2,FIND("(",A2)-1)
(ii) =LEFT("John(02457)",FIND("(",A2)-1)
(iii) =LEFT("John(02457)",FIND("("John(02457)")-1)
(iv) =LEFT("John(02457)",5-1) [Result as: John(0 ]
(v) =LEFT("John(02457)",4) [ Then Left function pull out 4 character from Left ]
(vi) =John [ Result ]
FormulaTo Extract E-Code
=RIGHT(A2,LEN(A2)-LEN(B2))
And the result will display (02457)
How This Formula Works ?
(i) =RIGHT(A2,LEN(A2)-LEN(B2))
(ii) =RIGHT("John(02457)",LEN(A2)-LEN(B2))
(iii) =RIGHT("John(02457)",LEN("John(02457)")-LEN(B2))
(iv) =RIGHT("John(02457)",11-LEN(B2))
(v) =RIGHT("John(02457)",11-LEN("John"))
(vi) =RIGHT("John(02457)",11-4)
(vii) =RIGHT("John(02457)",7) [ Then Right function pull out 7 character from Right ]
(viii) = (02457) [Result ]
Formula To Extract Name
=LEFT(A2,FIND("(",A2)-1)
And the result will display John
Keep the same above formula & you can change the "(" to "{" , "[", "#" or "@"
How This Formula Works ?
(i) =LEFT(A2,FIND("(",A2)-1)
(ii) =LEFT("John(02457)",FIND("(",A2)-1)
(iii) =LEFT("John(02457)",FIND("("John(02457)")-1)
(iv) =LEFT("John(02457)",5-1) [Result as: John(0 ]
(v) =LEFT("John(02457)",4) [ Then Left function pull out 4 character from Left ]
(vi) =John [ Result ]
FormulaTo Extract E-Code
=RIGHT(A2,LEN(A2)-LEN(B2))
And the result will display (02457)
How This Formula Works ?
(i) =RIGHT(A2,LEN(A2)-LEN(B2))
(ii) =RIGHT("John(02457)",LEN(A2)-LEN(B2))
(iii) =RIGHT("John(02457)",LEN("John(02457)")-LEN(B2))
(iv) =RIGHT("John(02457)",11-LEN(B2))
(v) =RIGHT("John(02457)",11-LEN("John"))
(vi) =RIGHT("John(02457)",11-4)
(vii) =RIGHT("John(02457)",7) [ Then Right function pull out 7 character from Right ]
(viii) = (02457) [Result ]