Hi, I'm looking for a formula to replace numbers with letters, but the string of numbers varies and only the numbers before the hyphen need to be changed. Here are some examples;
1135 would = AACE
5567 would = EEFG
23-804 would = BC-804
111-0535 would = AAA-0535
801-6654 would = HJA-6654
Attached is a worksheet with more examples. Thanks in advance.
Last edited by SPCC; 09-02-2020 at 01:23 PM.
The formula is really difficult to explain, please see the yellow area in your attachment
Simple formulas are not easy to complete this task
1. Completely write VBA code
2. The solution is custom function formula + formula combination
HTML Code:
Excel2003 cannot open .xlsx format
But if you need formula for Excel2003-2010
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A7,FIND("-",A7&"-")-1),$D$7,$E$7),$D$8,$E$8),$D$9,$E$9),$D$10,$E$10)
&MID(A7,FIND("-",A7&"-"),9)
Repeat 10 times of SUBSTITUTE which I only did 4 times
Excel 2013 or above
=BASE(SUMPRODUCT(MOD(MID(A7,ROW(A$1:INDEX(A:A,FIND("-",A7&"-")-1)),1)-1,10)+10,36^(FIND("-",A7&"-")-1-ROW(A$1:INDEX(A:A,FIND("-",A7&"-")-1)))),36)&MID(A7,FIND("-",A7&"-"),9)
Excel 2019 & MS365
=CONCAT(BASE(MOD(MID(A7,ROW(A$1:INDEX(A:A,FIND("-",A7&"-")-1)),1)-1,10)+10,36))&MID(A7,FIND("-",A7&"-"),9)
Last edited by Bo_Ry; 09-02-2020 at 02:32 AM.
Works perfect. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks