I'm trying to pull certain text out of a cell that's imported in. I'm having a hard time since it's very odd
A1:
8:30 PM EDTKC@ NE
I need to pull out just "KC" It's always going to be right after EDT and either 2 or 3 characters.
A2:
10:20 PM EDTLAC@ Denver
I need "LAC"
Formula:
MID(A1,FIND(" ",A1)+1,256)
This gives me everything that's after the first space. I'm not sure a better approach. Thank you!
Here's one way to do it.
Formula:=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99)),"EDT","")
Dave
Although this is simpler.
Formula:=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"@",REPT(" ",99)),"EDT",REPT(" ",99)),99,99))
=left(replace(a1,1,find("edt",a1)+2,""),find("@",replace(a1,1,find("edt",a1)+2,""))-1)
Ben Van Johnson
@Flameretired
That did work, the only thing is how can it be adjust so it only take a max of the first 3 letters. I have one that is EDTSEATTLE, and I only need SEA, but it's giving Seattle. I'm trying to figure in the formula where that could be represented.
Hi Beh,
This might be a bit shorter. See which you like better (the one that works!)
=TRIM(SUBSTITUTE(MID(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",20)),40,20)),4,3),"@",""))
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
How about B1 copied down...
=LEFT(MID(LEFT(A1,FIND("@",A1)-1),SEARCH("EDT",A1)+3,99),3)
HTH
Regards, Jeff
Similar to Marvin's.
Formula:=LEFT(TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("@",A1),99,""),"EDT",REPT(" ",99)),99)),3)
Yes!! Thanks excellent! - Marking it as solved
Thank you. It helps.Originally Posted by Beh162
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks