Place ** before and after the number

[niladri20052006]

Hi All,

I have a number like in one cell. Is it possible to place ** before and after each number

<table style="width: 179px; height: 40px;" border="0" cellpadding="0" cellspacing="0"><col width="150"><tr height="20"> <td style="height: 15pt; width: 113pt;" width="150" height="20">,408,650,831,415, 925 </td> </tr></table>

 

[wigi]

,408,650,831,415, 925

Are these 5 cells or 1?

 

[niladri20052006]

the numbers are in one cell.

I am trying level best to solve by by using formula

Please reply soon

 

[West Man]

Try: =SUBSTITUTE(A2,",","**")&"**"

 

[wigi]

Try something like this code:

Function WithStars(s As String)
    sq = Split(s, ",")
    For i = 0 To UBound(sq)
        If IsNumeric(Trim(sq(i))) Then soutput = soutput & "**,**" & Trim(sq(i))
    Next
    WithStars = Mid(soutput, 3) & "**"
End Function

Paste it in a normal module of your workbook. Then you can use the function in the sheet as follows:

=WithStars(A1)

if you want to let the function act on cell A1. Suit if needed.

Wigi

 

[Scott Huish]

As a UDF:

Function WithStars(r As String) As String
Dim t
r = Replace(r, " ", "")
t = Split(r, ",")
WithStars = Join(t, "*") & "*"
End Function

As a formula:

=SUBSTITUTE(SUBSTITUTE(A1," ",""),",","*")&"*"