Ms Excel : Need to replace last character and make single list

I have problem in replacing last character in excel sheet.

for example

input1: 34553CHP01A , input2: C
output: 34553CHP01A 34553CHP01B 34553CHP01C

input: columnA,columnB, output: columnC

i have to replace multiple rows like this.

kindly help me. thanks.....

2 Answers

Try putting this User Defined Function code in a standard public module code sheet then use it as you would any native Excel function on the worksheet.

Option Explicit
Function EXPAND_PARTNO(pno As String, n As String, _ Optional delim As String = " ") Dim m As String, i As Long, pnos As Variant pno = Trim(pno) m = Right(pno, 1) pno = Left(pno, Len(pno) - 1) ReDim pnos(Asc(m) To Asc(n)) For i = Asc(m) To Asc(n) pnos(i) = pno & Chr(i) Next i EXPAND_PARTNO = Join(pnos, delim)
End Function

If one has TEXTJOIN then one can use:

 =TEXTJOIN(" ",,LEFT(A2,LEN(A2)-1)&CHAR(ROW(INDEX($ZZ:$ZZ,CODE(RIGHT(A2))):INDEX($ZZ:$ZZ,CODE(B2)))))

Depending on one's version of Excel this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If one wants the values below each other in the same cell then change the " " to CHAR(10)

If they are to be in separate cells one below the other and one has the dynamic array formulas the just put this in the top cell:

=LEFT(A2,LEN(A2)-1)&CHAR(ROW(INDEX($ZZ:$ZZ,CODE(RIGHT(A2))):INDEX($ZZ:$ZZ,CODE(B2))))

And Excel will spill down.

If not and the user wants to fill down then put this in the first cell and copy/drag down:

=IF(ROW(INDEX($ZY:$ZY,CODE(RIGHT($A$2))))+ROW($A1)-1<=ROW(INDEX($ZY:$ZY,CODE($B$2))), LEFT($A$2,LEN($A$2)-1)&CHAR(ROW(INDEX($ZY:$ZY,CODE(RIGHT($A$2))))+ROW($A1)-1),"")