Extract multiple words from a cell in excel
Hello Everyone,
I am trying to extract certain words which starts from VRM in the cell. I've used below function but it extracts only the few instance where the condition is met and leaves the rest of word where the condition is met. I've attached a copy of the sample data for your reference.
=TRIM(MID(A4, FIND("VRM", A4), 12)) & ", " & TRIM(MID(A4, FIND("VRM", A4, FIND("VRM", A4) + 1), 12)) & ", " & TRIM(MID(A4, FIND("VRM", A4, FIND("VRM", A4, FIND("VRM", A4) + 1) + 1), 12))
Sample Data.xlsx
Are you still using Excel 2013?
If you have 365, clear ALL results in B2 and put this into B2:
=LET(data,A2:A6,BYROW(data,LAMBDA(r,TEXTJOIN(",",,TEXTBEFORE(TEXTSPLIT(r,CHAR(10))," ")))))
Last edited by AliGW; 10-10-2023 at 11:51 AM.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
UDF
=VRM(A2)
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Is this resolved?
Hello AliGW,
Thank You very much for your response it is working absolutely fine. I have a small request if could you please help me understand the formula how it functions and if I want to make any changes like for e.g.: in Row 4 I want to extract these values in output required column. Basically the unique in every row.
LLC - Services, LLC - Digital, LLC Limited - (UK) - Services, LLC - Generation, LLC - Risk
Also another quick question is this formula specific to column B only? Suppose I have a similar data in column D and I need output in column E will it not work because when I copy paste the formula and change the range it is not working. The data is another cell is like
INSURANCE CO., LTD - VTS - VRM318491196 - 2023 - 2029340
Thanks,
Ritu Raj
Last edited by rituraj851; 10-11-2023 at 07:40 AM.
Hello John,
Thank You for your response. Sorry, I am not familiar with VBA programming on how to use it and get results. I tried to copy, paste and execute the code in VB editor but nothing worked.
Thanks,
Ritu Raj
Please show your EXPECTED results (referred to in Post 5) in the Excel file, as I do not understand what you want.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hello Glenn,
Please refer to the Output required2 column attached. I want to extract these values in output required column. Basically the unique in every row
LLC - Services, LLC - Digital, LLC Limited - (UK) - Services, LLC - Generation, LLC - Risk
You need to explain your expected answers for the GREEN shaded cells. Why are certain values EXCLUDED from your expected results?
My first attempt formula is in column B.
Hello Ali,
Thank You very much for your response it is working absolutely fine. I have a small request if could you please help me understand the formula how it functions and if I want to make any changes like for e.g.: in Row 4 I want to extract these values in output required column. Basically the unique in every row.
LLC - Services, LLC - Digital, LLC Limited - (UK) - Services, LLC - Generation, LLC - Risk
Also another quick question is this formula specific to column B only? Suppose I have a similar data in column D and I need output in column E will it not work because when I copy paste the formula and change the range it is not working. The data is another cell is like
INSURANCE CO., LTD - VTS - VRM318491196 - 2023 - 2029340
Thanks,
Ritu Raj
I was giving an example to extract unique example
When replying... please make it CLEAR who you are talking to. I offered a solution, with a question, and I have NO IDEA if you have looked at my reply, or answered my question.
Hi Glenn,
Apologies for not being clear in my response. I was responding to your question in post 11.
Administrative Note:
Unfortunately, it has come to our attention you have violated Guideline #3 of our Forum RULES:
Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.
All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.
Breaking this guideline is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to a permanent ban, so do take this caution to heart.
Sorry I was not aware of guideline. I will keep in mind going forward.
If you want help with this, provide more realistc sample data.
Please refer to the sample data attached.
I have data in column A where I need to extract word starting with VRM in column C. When I copy paste the formula and change the range it is not working. It returns the result as first word in column A
In C2:
=TEXTBEFORE(TEXTAFTER(A2:A6,"TAV - ")," -")
You might need this, judging by earlier posts:
=TEXTBEFORE(TEXTAFTER(A2:A6," - ")," -")
And I had asked for REALISTIC data!
Seen and gone without a response ...
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Hello Ali,
Thank You very much for your support. As you mentioned in post #19 sorry I am not able to share the exact realistic data because it is proprietary information which I am not allowed to share on a public forum which is why I had to prepare a dummy data for my scenario. Hope you can understand.
Apologies for the delay I was occupied a bit with some stuff which needed immediate attention. Thank you for you guidance on my query and the process to close the thread. I'll do that. And apologies once again for violating Guideline #3 of the Forum.
Thanks,
Ritu Raj
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks