Data is in B4:B500, something like this :
12345678/001-name1
text1
22558877/089-name2
33445588/012-name3
text2
text3
11992288/004-name3
Result should be consecutive in Column C
12345678/001
22558877/089
33445588/012
11992288/004
If possible without help column (I have too many already)
Last edited by ionelz; 05-10-2020 at 10:04 AM.
Maybe it's clear for you, but just to make sure.
Is it the data between the / and the - that you want to extract?
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Hi ionelz,
I'd be looking at an Advanced Filter using a criteria of */* and put the results in column C. Does that make sense?
Then I'd do a Left() function to get text left or the "-".
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
The expected results are in the second half of the post.
If you want to update the column itself then you will need VBA.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Keelbellah,
If you look at Results in my first post, EXTRACTED are ONLY Rows which contain "/", and in that extracted rows, data in LEFT side of "-"
Last edited by ionelz; 05-10-2020 at 12:11 PM.
I saw that but you should clearly mention what YOU ate seeking not what others assume
Don’t let others guess or think up things you probably don’t want done
With respect, Keebllah, the OP was clear in the opening post about the results he is looking for:
He also mentioned not wanting a helper column, which is why I made the comment about VBA.Result should be consecutive in Column C
12345678/001
22558877/089
33445588/012
11992288/004
Please try at C4
=IFERROR(LEFT(INDEX(B:B,AGGREGATE(15,6,ROW($B$4:$B$500)/ISNUMBER(SEARCH("/*-",$B$4:$B$500)),ROWS(C$4:C4))),12),"")
Bo_Ry, like always, billions of thanks !!!
How many times so far, you just came, read the question, and answer !?!?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
One moment please, I will ask him another question if possible !
Bo, please look in Column C
I have replaced fixed "12" with Floating FIND("-",B2)-1 because data in B have leading spaces
Now, I do not get the result consecutive (aggregate) in C
Last edited by ionelz; 05-10-2020 at 01:55 PM.
Please add trim
=IFERROR(LEFT(TRIM(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$17)/ISNUMBER(SEARCH("/*-",$B$2:$B$17)),ROWS(C$2:C2)))),12),"")
Thank you, problem solved !
If you could EXTRACT in Column C, as they are in B (so without TRIM) that would Help me, COUNT the Leading spaces in E
I need that column E, and I am not sure if I can do that based on data from B
But thank you very much
D2
=iferror(index(left($b$2:$b$17,find("-",$b$2:$b$17)-1),aggregate(15,6,row($b$2:$b$17)/isnumber(search("/*-",$b$2:$b$17)),rows(d$2:d2))-row($b$1)),"")
Thank you so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks