Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Need to do a fancy string copy plus
There are probably several ways to do this. One way does not involve VBA: 1. Select all the cells in Col A containing data. 2. Click on EditReplace and enter < in the "Find" window and ( in the "Replace With" window and click on "Replace All". 3. Repeat this process with & ) . 4. In Cell B2 (assuming data starts in row 2) enter the formula: =CONCATENATE("<write",A2,"") 5. Copy this formula down for all rows with data in Col A. 6. Select all cells in Col B, then CopyPaste SpecialValues. You will then have what you want I believe. If not, post back. -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=481858 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Updating master workbook from source that may/may not exist | Excel Worksheet Functions | |||
Copy worksheets with formulas between different workbooks | Excel Discussion (Misc queries) | |||
Copy Function | Excel Discussion (Misc queries) | |||
how to copy 2350 hyperlink full paths to any column in a worksheet ? | Excel Discussion (Misc queries) |