Need to do a fancy string copy plus

[Sally]

PLEASE save me 8,000 hours of manual work. I know one of you can figure this
out - I sure can't. Thanks for looking.
I don't know vba at all so if you want me to use it please be specific about
what to type where.

I have a million rows with data in column A that looks like this (including
the brackets):

<aaaaaa><bbbbbb>
<cccccc>111111111111
<ddddddddddddd>

And I need column be to copy with additional text like this
<aaaaaa><writeaaaaaa)><bbbbbb><writebbbbbb)>
<cccccc><writecccccc)>111111111111<write:111111111111>
<ddddddddddddd><writeddddddddddddd)>

Basically, for every string surrounded by <>,
put the following in column B
copy the string surrounded by <> but change the <> to ()
add "<write:" in front of the string that starts with (
put a > after the )

for any strings in column A with no <> around it
copy the string and
add "write:" in front of it
put a > after it

There can be more than on string with or without <> in column A
If there is, everything in Column A needs to be in column B following the
rules above.

 

[Sally]

It gets worse. There are sometimes blank spaces in front of these strings
of text and they need to be copied as is into column B.

 

[Tom Ogilvy]

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><writeaaaaaa)><bbbbbb><writebbbbbb)>
<cccccc><writecccccc)>111111111111<write:111111111111>
<ddddddddddddd><writeddddddddddddd)>

that appears to match what you showed.

 

[HotRod]

What is the name of your worksheet? Do you want to just email it to me?

 

[HotRod]

Do you want to post 10-20 rows of data for us to use?

 

[PCLIVE]

As long as all your data will be in one of those formats, (either 2 strings
with <>, 1 string with 1 <> 1 without, or 1 string with <>), here's one
crazy formula that seems to work.

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"<",""))=2,LEFT(A1,FIND(">",A1))&"<write:"&SUBSTITUTE(LEFT(SUBSTITUTE(LEFT(A1,FIND(">",A1)),">",")"),FIND(">",A1)),"<","(")&">"&RIGHT(A1,FIND(">",A1,FIND(">",A1)+1)-FIND(">",A1))&"<write:"&SUBSTITUTE(SUBSTITUTE(RIGHT(A1,FIND(">",A1,FIND(">",A1)+1)-FIND(">",A1)),"<","("),">",")")&">",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"<",""))=1,IF(LEN(A1)>FIND(">",A1),LEFT(A1,FIND(">",A1))&"<write:"&SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(">",A1)),"<","("),">",")")&">"&RIGHT(A1,LEN(A1)-8)&"<write:"&RIGHT(A1,LEN(A1)-8)&">",LEFT(A1,FIND(">",A1))&"<write:"&SUBSTITUTE(LEFT(SUBSTITUTE(LEFT(A1,FIND(">",A1)),">",")"),FIND(">",A1)),"<","(")&">")))

This one was fun.
Paul


news:[email protected]...

 

[Tom Ogilvy]

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
if Left(cell.Value,1) = " " then
cell.offset(0,1).Value = cell.Value
else
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
End if
Next cell
End Sub