How to Replace Nos {1,2,3,4,5,6,7,8,9} with some text appended to the number and a comma?

How to Replace Nos {1,2,3,4,5,6,7,8,9} with some text appended to the number and a comma?

Dear Forum,

If I have a value in a cell such as 12345 then I need that to be shown as below:

Sheet1,Sheet2,Sheet3,Sheet4,Sheet5

If the value is 12040 then I need to just show:

Sheet1,Sheet2,Sheet4

I dont how to use the Replace, Substitute Function for a single character?

Warm Regards
e4excel

A bunch of nested SUBSTITUTE Functions should work:

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"Sheet1 "),2,"Sheet2 "),3,"Sheet3 "),4,"Sheet4 "),5,"Sheet5 "),6,"Sheet 6"),7,"Sheet 7"),8,"Sheet8 "),9,"Sheet9 "),0,""))," ",",")

The cell reference appears only once in inner most nested function

This formula replaces all 0's... as long as you don't enter anything for the 4th option argument (instance_num), then all 0's should get replaced (make sure they are not the letter O instead).

Substitute is not that versatile..

If you are willing to use VBA, the use this udf:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

and then formula:

=SUBSTITUTE(TRIM(SUBSTITUTE(aconcat("Sheet"&MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)," "),"Sheet0"," "))," ",",")

confirmed with CTRL+SHIFT+ENTER not just ENTER

If you have teh Morefunc.xll addin, then you can use:

=SUBSTITUTE(TRIM(SUBSTITUTE(MCONCAT("Sheet"&MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)," "),"Sheet0"," "))," ",",")

confirmed with CTRL+SHIFT+ENTER not just ENTER

As you may have noted the UDF is not mine:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
.
.
.

so I won't take credit... but it is a popular piece of code used for this purpose.

This part ROW(INDIRECT("1:"&LEN(A1))) when part of an array formula, simply creates an array from 1 to the length of the string in A1, so that exactly the number of characters found in A1 are checked in the MID function one at a time.

Not sure what your question is?

If you use the Evaluate formula tool found in the Formulas tab, you will see how it breaks out.

If you have 13579, then ROW(INDIRECT("1:"&LEN(A1))) breaks down to: ROW(INDIRECT("1:"&5)) where 5 is the length of the text 13579. That further breaks to ROW($1:$5) and when applied to MID(A1,ROW($A$1:$A5),1), it expands out to MID(A1,{1,2,3,4,5},1) so now each of the five characters in A1 gets extracted by the MID function so you get {"1","3","5","7","9"} and each gets concatenated to the word "Sheet", even if there is a 0 in the number... there is another SUBSTITUTE added to clear out any "Sheet0" that may be included, leaving only the desired text string.