Hi ,
I'm trying to capture the last part of the data and trim-off everything before the 3rd occurrence of the "_" . (i.e- I'm wanting to keep the "Henry" & "Sam" parts of the metadata). Any help would be much appreciated
Data:
ZV163_01_RogueWaves_Henry
ZV178_23_RedDawn_Sam
non VBA solution
Formula:
Please Login or Register to view this content.
essentially
find the 3rd occurrence of _ and replace with blank space (char 1)
then mid formula to start from this point + 1
extend 50 if you require longer names? or smaller if you dont require so much space
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
never mind
Ben Van Johnson
Thanks humdingaling , but I'm going to have to do this on thousands of rows with varying data, so I don't know how I'd turn the 3rd occurrence into a space on all of them
I know a basic find and trim formula [ =TRIM(RIGHT(A1,FIND("_",A1))) ] , but I'm thinking there must be a way to make one that will work by finding the 3rd occurrence and then just fill it down the sheet
I figured it out. This one is working :
=TRIM(RIGHT(SUBSTITUTE(A2,"_",REPT(" ",999)),999))
the formula i provided automatically did what i was explaining without the trim
As i was not aware of your full data set
your conditions was 3rd occurrence as i don't know if you may have 4th of 5th in your data set
hence why i used Mid instead of right =)
your formula works by "stretching" the last occurrence out to the point the text after 3rd _ is "separated" from the rest of the text
you probably do not need 999 spaces.....100 should be more than suffice
will make your sheet run better
however if there is a 4th occurence of _ your formula will fail
either way as long as it works for you
or,
Please Login or Register to view this content.
I'm sorry humdingaling , I implemented it incorrectly somehow.. The one you made totally works great! As does yours protonLeah. Thank you both !
not a problem
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Cheers
hum
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks