i appreciate immediate response
formula is best choice
get the text from one string
Hi Formula Gurus,
In this attachment, i try to get one particular in a bunch of string, i have a list of text which the string having that one text, i can able to find by using "text to columns", the problem is the text is in different entity. so i used the formulas i showed in the sample file to identify my desired text in that string. I want this entire calculation in one formula.
hopefully u understand what i am trying to propose.
thanks in advance
Last edited by jaffirahamed1; 02-09-2011 at 07:25 AM. Reason: My posted has solved
This in B2
=LEFT(SUBSTITUTE(SUBSTITUTE(A2,"ZG_HU_",""),"ECC_",""),4)
Drag/Fill Down
Will work on your sample, but somehow I think there is more to your question than the sample suggests.
Hope this helps
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
@jaffirahamed1
do not create meaningless polls
your original thread title is not appropriate - "test for formula gurus" adds no value.
given your lack of posts I have modified the title on this occasion - going forward please ensure they are suitable.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi Marcol
Some of the business roles are 5 characters, so that solution is unlikely to work with the whole data range.
Also, one of the roles is ZG_HA rather than HU.
If you are serious about finding a solution.
You would do best to post a sample workbook showing your Sheet Layout and perhaps Before and After examples coveringy the string types and what you need extracted.
It should clearly illustrate your problem and not contain any sensitive data.
Simply put, there is insufficiant information at present to solve your problem.
just return 5 chars and delete the _
=SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(A2,"ZG_HU_",""),"ECC_",""),5),"_","")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Not sure that works Martin
Best I can see at the moment is
Please Login or Register to view this content.
Or to simplify use two columns
B2
C2Please Login or Register to view this content.
Please Login or Register to view this content.
But I'm pretty sure there will be more wobblies yet.
[EDIT]
Where the Check list shows FALSE the source string was changed for testing for errors.
And
Some of the business roles have more than 5 characters
Last edited by Marcol; 02-09-2011 at 06:10 AM. Reason: Added attachment
Perhaps:
Please Login or Register to view this content.
Re: get the text from one string
Hi Marcol and martin,
Thanks for you to spending time for my post. it seems that in the "ROLE" the first 5 letter are not constant it may vary more count
eg.
ZG_HU_ECC
ZG_HA_ECC
ZG_AT_ECC
ZG_HA_BIW
ZG_RO_SRM
ZG_RO_ECC
ZG_RO_BIW
ZG_BIW_MD
ZG_BIW_CR
ZG_HA_SRM
ZG_HA_CRM
ZG_RO_CRM
ZG_HU_CRM
ZG_FR_ECC
ZG_SK_BIW
ZG_UA_ECC
ZG_SK_ECC
ZG_SL_ECC
ZG_HR_ECC
ZG_BIW_CO
ZG_US_GMR
ZG_ECC_FI
ZG_FR_BIW
ZG_BIW_MF
Your example shows based on this criteria, its seems very difficult to put every entity, again i want the output in single formula, not for compute with more than one column.
The formula in post # 8 replicates your expected output (sample file).
If still not correct I'd suggest posting back with a revised sample file to better illustrate requirements.
Hi DonkeyOte,
Its very nice , your formula works awesome. Ur are very excellent in excel, so many difficult solutions made by you. Actually i dont have words to express about this. Keep going man, my dear EXCEl GEENIUS.
regards
Jaffir
Jaffir, I'm just regurgitating stuff I've picked up from others, working out who came up with the solutions first (and is a genius) is the hard part !
I suspected this would be the case from the outset.(The new conditions in Post #9)
@ Don
Your solution is by far the best,and still works with the newest conditions, one minor observation.
If the source string has extra characters trailing, in error, a code that is in the lookup list then there might be a problem, (It might not be possible to have errors in the source string. I generated some for testing - see line three in my attachment Post #7)
Last edited by Marcol; 02-09-2011 at 07:58 AM.
Not sure I follow - if the codes don't exist then you would get an error - that is to be expected, no ?
If multiple Role IDs exist within the string then the method outlined will return the last listed (List sheet)
Re: avoiding partial matching / false positives, if as implied the Role IDs are always encased within underscore then it would be better to adjust accordingly
this is something I should have added to the original suggestion, not doing so was an oversight on my partPlease Login or Register to view this content.
That cures it Don.
Originally if CRXD11 is in the list and say CRXD11# isn't, and is either an error or a new code, CRXD11 was being be returned instead of #N/A.
Cheers
Re: get the text from one string
Hai Marcol and Don,
Thanks for your valuable thread, and that is more informative, i will enhance my excel based on your solutions.
cheers
JJ
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks