Welcome to my Excel Power Tips Channel!
My latest tip shows the step-by-step process of building an Email Address extraction from any length text formula.
Here are the steps I used which you can use yourself for your work:
STEP 1: Formula to replace (substitute) every space with 25 spaces cell A2
------------------------------------------------------------------------------------------------------------------------
=SUBSTITUTE(A2," ", REPT(" ",25) )
STEP 2: Formula to find position of @ sign in STEP 1 string in cell A2
-------------------------------------------------------------------------------------------------------------
=FIND( "@", SUBSTITUTE(A2," ", REPT(" ",25) ) )
STEP 3: Extract 25 characters before @ Sign of Step 1 String using the position of @ sign returned in Step 2
-------------------------------------------------------------------------------------------------------------
=MID( SUBSTITUTE(A2" ",REPT(" ",25) ), FIND( "@", SUBSTITUTE(A2" ", REPT(" ",25) ) ) - 25, 50 )
STEP 4 (FINAL): Remove extra spaces in Step 3 using TRIM function
--------------------------------------------------------------------------------------------------------------
=TRIM( MID( SUBSTITUTE(A2," ", REPT(" ",25) ), FIND( "@", SUBSTITUTE(A2," ", REPT(" ",25) ) ) - 25, 50 ) )
Enjoy!
Like - Subscribe - Share
21 авг 2024