Hi!
In the attached file, column D has the classification for stocks. I would like to write a formula and break up the first, second and third string as shown in columns F, H, J highlighted in yellow. What is giving me trouble is the second " | " which I can't seem to select properly. Any help would be much appreciated.
Thank you.
You can use this formulae in the cells stated:
E2: =IF(COUNTIF(D2,"*|*"),LEFT(D2,FIND("|",D2)-2),"")
H2: =IF(COUNTIF(D2,"*|*"),MID(D2,FIND("|",D2)+2,FIND("|",D2,FIND("|",D2)+1)-2-FIND("|",D2)),"")
J2: =RIGHT(D2,3)
Then copy down as required.
Hope this helps.
Pete
Thank you so much! it works well.
You used the "*|*" to tell excel to look for the second term?
Not quite - that just detects if there is a pipe character in the text. If not, it returns a blank to that cell.
Pete
Do you know why the countif returns a 1 when there are clearly two | in the cell?
Is one of them right at the start or right at the end of the entire string?
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.
This is the entire cell: Technology | Semiconductor Equipment & Materials | Netherlands.
I'm not sure why he/she is using the asterisk in countif(D4.Find"*|*")
Hi there!
J2 - only works if the last initials are 3 letters. There are multiple countries in my list of stocks, e.g (Netherlands). The formula = Left(D4, 3), only pulls "nds". How can I modify it so it pulls "Netherlands".
Please let me know if I need to upload the spreadsheet again.
Thank you!
Last edited by smart_beta; 07-05-2021 at 03:46 AM.
Some countries have multiple-word names. The Netherlands being one of them. Are there multiple-word country names in your dataset?
It would help if you uploaded a REPRESENTATIVE dataset, instead of one that just looks a bit like the real situation.
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
In J2
=TRIM(MID(D2,FIND("/",SUBSTITUTE(D2," ","/",LEN(D2)-LEN(SUBSTITUTE(D2," ","")))),30))
in the last entries there are no delimiters ("|") so what is the result: all blank? If so, above needs to be changed to ....
=IF(COUNTIF(D2,"*|*"),TRIM(MID(D2,FIND("/",SUBSTITUTE(D2," ","/",LEN(D2)-LEN(SUBSTITUTE(D2," ","")))),30)),"")
See post #9
Last edited by JohnTopley; 07-05-2021 at 04:51 AM.
it works well!
Nice little trick there finding the number of blank spaces and swapping the last space with a "/" to count the instances. Then finding the position of the / to extract the MID.
Thank you for teaching me some cool tricks!
Much appreciated!
This is better as it caters for "New Zealand"!
=IF(COUNTIF(D2,"*|*"),TRIM(MID(D2,FIND("/",SUBSTITUTE(D2,"|","/",LEN(D2)-LEN(SUBSTITUTE(D2,"|",""))))+1,30)),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks