Need to do a fancy string copy plus
 

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
<cccccc111111111111
<ddddddddddddd

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)

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.

Need to do a fancy string copy plus
 

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.


"Sally" wrote in message
...
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
<cccccc111111111111
<ddddddddddddd

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)

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.

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)

Need to do a fancy string copy plus
 

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


"Sally" wrote in message
...
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
<cccccc111111111111
<ddddddddddddd

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)

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.

Need to do a fancy string copy plus
 

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


"Sally" wrote in message
...
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
<cccccc111111111111
<ddddddddddddd

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)

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.

Need to do a fancy string copy plus
 

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)),"<","(")&""&RIGH T(A1,FIND("",A1,FIND("",A1)+1)-FIND("",A1))&"<write:"&SUBSTITUTE(SUBSTITUTE(RIGH T(A1,FIND("",A1,FIND("",A1)+1)-FIND("",A1)),"<","("),"",")")&"",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"<",""))=1,IF(LEN(A1)FIND("",A 1),LEFT(A1,FIND("",A1))&"<write:"&SUBSTITUTE(SUBS TITUTE(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)),"",")"),FIN D("",A1)),"<","(")&"")))

This one was fun.
Paul


"Sally" wrote in message
...
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
<cccccc111111111111
<ddddddddddddd

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)

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.

Need to do a fancy string copy plus
 

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

--
Regards,
Tom Ogilvy

"Sally" wrote in message
...
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.


"Sally" wrote in message
...
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
<cccccc111111111111
<ddddddddddddd

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)

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.