I have 2 tickers both ending with s as per below
ITU3 BS
AXSB IS
I need to change the first ticker to end with Z and the 2nd ticker to end with N. I need to use only one formula to do so. What I mean is that if I put that same formula in cells A1 and A2 while the tickers will be in B1 and B2, the results will be ITU3 BZ and AXSB IN in A1 and A2.
Thanks!
hi Falcongbz, welcome to the forum. you probably got to provide more info. is it always 7 characters long? is it changed to "Z" because 6th letter is "B"? is it changed to "N" because 6th letter is "I"? any more scenarios? if i guessed those correctly, then:
=IF(MID(B1,6,1)="B",LEFT(B1,6)&"Z",LEFT(B1,6)&"N")
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thanks benishiryo. The ticker can be anywhere between 5-7 characters. It will always need to change to Z because the second last letter is B and it will always need to change to N because the second last letter is I. I hope it makes sense.
=left(b1,len(b1)-1)&choose(row(b1),"z","n")
then try:
=IF(MID(B1,LEN(B1)-1,1)="B",LEFT(B1,LEN(B1)-1)&"Z",LEFT(B1,LEN(B1)-1)&"N")
remember to mark this as "Solved" once done
Thanks again teethless mama and benishiryo. I am going to try and be as specific as possible in terms of what I need to make sure there is a way to solve this. I have a long list of tickers, for some of the tickers I would like to change the ending for others they should stay the way they are. Following are the tickers ending that need to get changed ITU3 BS---ITU3 BZ, AXSB IS---AXSB IN, ANDINAB CC---ANDINAB CI, BCOLO CX---BCOLO CB, BAP UN---BAP US, COMI EC---COMI EY, CEZ CK---CEZ CP (to sum it up S-Z, S-N, C-I, X-B, N-S, C-Y, K-P). There are more than one ticker with the same ending (BZ, IS, CC etc...). Number of charters varies between different tickers. All other tickers with different endings should remain the same.
Hope there is one formula to solve this.
Thanks.
Hi,
I think it can be solved with lookup table. I listed your letters as a table and used that to change last table.
=LEFT(D2,LEN(D2)-1)&VLOOKUP(RIGHT(D2,1),$A$2:$B$8,2,0)
The only problem here is, there are 2 letets for letter S and vlookup will pick only one.
Uploading file here
Appreciate the help? CLICK *
Thanks. Really close but in need that differentiation in the two different different S endings.
What is the difference between them. I mena Z after S and N after S.
What is the logic behind this letters. Under what condition the last letter is Z or N?
If the second last letter is "I" then the "S" should be converted to "N". If the second last letter is "B" then the "S" should be converted to "Z". Thanks.
OK
I added one new lookup column to old one. And my formula is:
=LEFT(E2,LEN(E2)-1)&INDEX($C$2:$C$8,MATCH(1,(MID(E2,LEN(E2)-1,1)=$A$2:$A$8)*(RIGHT(E2,1)=$B$2:$B$8),0))
Its an array formula, You should enter Ctrl+Shift+enter at the same time.
Column table data taken from your messages. Youcan change them.
I put the formula as an array formula to the spreadsheet you provided and I get #N/A. Would you be able to send me the spreadsheet with the formula in it? Thanks again.
Spreadsheet is in my previous message, You can download it from thereOriginally Posted by Falcongbz
The spreadsheet you attached has the old formula. When I put the new one in it I get the error message I mentioned before. Thanks.
A2:A8 = First criteria column (second letter from last
B2:B8 = Second criteria column (last character
C2:C8 = The letter that will be replaced
E2:E8 = You words that should be changed
Formula to g2 = =LEFT(E2,LEN(E2)-1)&INDEX($C$2:$C$8,MATCH(1,(MID(E2,LEN(E2)-1,1)=$A$2:$A$8)*(RIGHT(E2,1)=$B$2:$B$8),0)) Ctrl+Shift+Enter
Getting close. Thanks so much for your help but when I add other tickers (which I have to) that don't need to be changed than I get the error message again. I have a bunch of other tickers that don't need to be changed I just added 2 for example. So the formula if possible should be able to handle that. I am attaching the file with the 2 additional tickers. Thanks.
enter this formula to G2 cell
=IFERROR(LEFT(E2,LEN(E2)-1)&INDEX($C$2:$C$8,MATCH(1,(MID(E2,LEN(E2)-1,1)=$A$2:$A$8)*(RIGHT(E2,1)=$B$2:$B$8),0)),E2) CSE then drag down
Means if the result is error then bring original value
enter this formula to G2 cell
=IFERROR(LEFT(E2,LEN(E2)-1)&INDEX($C$2:$C$8,MATCH(1,(MID(E2,LEN(E2)-1,1)=$A$2:$A$8)*(RIGHT(E2,1)=$B$2:$B$8),0)),E2) CSE then drag down
Means if the result is error then bring original value
Hi AZ-XL your formula works great! Thank you! I do have one more question, can an exclusion be added to the formula? There is one ticker VTBR RX that does not need to change to VTBR RU. Can that be some how added to the formula? Thanks again.
=if(e2="VTBR RX",e2,IFERROR(LEFT(E2,LEN(E2)-1)&INDEX($C$2:$C$8,MATCH(1,(MID(E2,LEN(E2)-1,1)=$A$2:$A$8)*(RIGHT(E2,1)=$B$2:$B$8),0)),E2))
Great Works!!!! Thank you so much. Really appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks