Separating Characters with Asterisk

[Robert_Conklin]

I have an excel workbook that uses VBA and user-forms that my end-users can submit maintenance requests for machines and spare parts. The cell that contains the manufacturer part number submitted needs to have an asterisk placed between each character in the cell. For instance: the mfg part number submitted is 562314578, and in another cell should be converted to *5*6*2*3*1*4*5*7*8*

Thanks in advance,
Rob

 

[Rick Rothstein]

Give this formula a try...

=TEXT(A1,REPT("\*0",LEN(A1)))&"*"

 

[Robert_Conklin]

Thanks Rick, the formula works, but I need it to incorporate into my VBA code.

 

[Rick Rothstein]

Thanks Rick, the formula works, but I need it to incorporate into my VBA code.

You did not give us any layout information, so I don't know if you want this for a single cell or for a range of cells and, if a range of cells, I don't know whether the range is horizontal or vertical. Here is a UDF (user defined function) that you can call from within your own code or from a worksheet cell (you apparently would want the former). Simply pass the value or cell reference for the text you want to add the asterisks to and the function will return the passed value with the asterisks inserted...

Function AddAsterisks(S As String) As String
  AddAsterisks = Format(S, Application.Rept("\*0", Len(S))) & "*"
End Function

 

[Norie]

Not quite sure it gives you exactly what you want but try this.

Range("A1").Value = Replace(StrConv(Range("A1").Value, vbUnicode), Chr(0), "*")

 

[Rick Rothstein]

Not quite sure it gives you exactly what you want but try this.

Range("A1").Value = "*" & Replace(StrConv(Range("A1").Value, vbUnicode), Chr(0), "*")

I think if you add what is shown in red above, it will work (assuming a single cell reference being required).

 

[Robert_Conklin]

I do have a table set up for the user-form (=$A$1:$AL$2). Column O is where the mfg part number is written. Column AL is titled SAP SEARCH CRITERIA, where the data from column O should be converted with the asterisk.

As a new entry is submitted through the user-form, another record in the table is created.