Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fancy lookup | Excel Worksheet Functions | |||
Need to do a fancy string copy plus | Excel Discussion (Misc queries) | |||
Need some fancy graphics | Excel Programming | |||
fancy forms | Excel Programming | |||
Fancy Object Declaration | Excel Programming |