View Single Post
  #3   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Need to do a fancy string copy plus

With only a copy of your workbook open,
in Excel, do Alt+F11
then in the menu choose Insert=Module

paste in this code below

Do Alt+F11 to go back to excel

The page with your data in Column A starting in A1 should be the active page

then go to the menu and choose Tools=Macros=Macro

Select FixDate and click the Run button.

Sub FixDate()
Dim rng As Range, cell As Range
Dim sStr As String, s As String
Dim v As Variant, i As Long
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For Each cell In rng
sStr = Replace(cell.Value, "<", "|(")
sStr = Replace(sStr, "", ")|")
If Left(sStr, 1) = "|" Then _
sStr = Right(sStr, Len(sStr) - 1)
If Right(sStr, 1) = "|" Then _
sStr = Left(sStr, Len(sStr) - 1)
sStr = Replace(sStr, "||", "|")
v = Split(sStr, "|")
s = ""
For i = LBound(v) To UBound(v)
s = s & Replace(Replace(v(i), _
"(", "<"), ")", "") & "<write:" _
& v(i) & ""
Next i
cell.Offset(0, 1).Value = s
Next cell
End Sub

With your test data, this produced:
<aaaaaa<write:(aaaaaa)<bbbbbb<write:(bbbbbb)
<cccccc<write:(cccccc)111111111111<write:1111111 11111
<ddddddddddddd<write:(ddddddddddddd)

that appears to match what you showed.
--
Regards,
Tom Ogilvy



And I need column be to copy with additional text like this
<aaaaaa<write:(aaaaaa)<bbbbbb<write:(bbbbbb)
<cccccc<write:(cccccc)111111111111<write:1111111 11111
<ddddddddddddd<write:(ddddddddddddd)