Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Hi,
My excel file Cell A1 has the following format (Multiple numbers in diff rows) 1234 23244 4434 121 1442 534 121223 12 How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434 (Cell C1) etc.. ? Thanks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Wed, 26 Dec 2007 18:14:00 -0800, Jeff
wrote: Hi, My excel file Cell A1 has the following format (Multiple numbers in diff rows) 1234 23244 4434 121 1442 534 121223 12 How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434 (Cell C1) etc.. ? Thanks for your help. You can use a UDF to do this. To enter this <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then insert/module and paste the code below into the window that opens. Select your data range. Then <alt-F8 and RUN the Rearrange macro. As written, the numbers are returned as numeric values, so leading zeros will not be present. If you require preservation of leading zero's, then note the comment to change the format of the destination cells. Also note that if any of your Selected data range is in any of the destination cells, they will be overwritten and not recoverable. So backup your data. ================================================= Option Explicit Sub Rearrange() Dim c As Range Dim sTemp Dim i As Long For Each c In Selection sTemp = sTemp & c.Text & " " Next c sTemp = Application.WorksheetFunction.Trim(sTemp) sTemp = Split(sTemp, " ") Set c = Range("A1") For i = 0 To UBound(sTemp) With c(1, i + 1) .NumberFormat = "General" 'use numberformat @ for returning values as text '.NumberFormat = "@" .Value = sTemp(i) End With Next i End Sub ============================================ --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
One play which would bring you close ..
Assuming the data as posted is in A1:A3 Select A1:A3, click Data Text to Columns, delimited. In step 2, check "Space" Finish. This splits the data into cols A to C. Then to extract the contents in A2:C3 into row 1, you could place this in D1: =OFFSET($A$2,INT((COLUMNS($A:A)-1)/3),MOD(COLUMNS($A:A)-1,3)) Copy D1 across to I1. Kill the formulas with an "in-place" copy n paste special as values. Clean up by clearing A2:C3. The "3" in the OFFSET formula refers to the number of source cols (in the above, its 3 cols - cols A to C). Adjust to suit. A better way might be to extract the split data in cols A to C down a col, instead of across a row. Eg you could place this instead in say E2: =OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) then copy E2 down as far as required to exhaust the source data, kill the formulas & clean up by using autofilter on col E, filter for zero, and delete all zero lines, remove autofilter. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeff" wrote: Hi, My excel file Cell A1 has the following format (Multiple numbers in diff rows) 1234 23244 4434 121 1442 534 121223 12 How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434 (Cell C1) etc.. ? Thanks for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Hi Max, thanks for your reply.
The data is in cell A1 only. Not A1:A3. If using your method, original data 1234 23244 4434 121 1442 534 121223 12 will change to A B 1 1234 23244 where next row data 4434 121 1442 & 534 121223 122 will be gone. The output should be A B C D E F G H 1 1234 23244 4434 121 1442 534 121223 12 Rgds..Jeff "Max" wrote: One play which would bring you close .. Assuming the data as posted is in A1:A3 Select A1:A3, click Data Text to Columns, delimited. In step 2, check "Space" Finish. This splits the data into cols A to C. Then to extract the contents in A2:C3 into row 1, you could place this in D1: =OFFSET($A$2,INT((COLUMNS($A:A)-1)/3),MOD(COLUMNS($A:A)-1,3)) Copy D1 across to I1. Kill the formulas with an "in-place" copy n paste special as values. Clean up by clearing A2:C3. The "3" in the OFFSET formula refers to the number of source cols (in the above, its 3 cols - cols A to C). Adjust to suit. A better way might be to extract the split data in cols A to C down a col, instead of across a row. Eg you could place this instead in say E2: =OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) then copy E2 down as far as required to exhaust the source data, kill the formulas & clean up by using autofilter on col E, filter for zero, and delete all zero lines, remove autofilter. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeff" wrote: Hi, My excel file Cell A1 has the following format (Multiple numbers in diff rows) 1234 23244 4434 121 1442 534 121223 12 How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434 (Cell C1) etc.. ? Thanks for your help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Hi Ron, thanks for your response
I tried your method below and the output as follow A B C D E F 1 1234 23244 121 1442 121223 12 4434 534 2 3 Where cell B2 and D2 still merge the numbers. Rgds..Jeff "Ron Rosenfeld" wrote: On Wed, 26 Dec 2007 18:14:00 -0800, Jeff wrote: Hi, My excel file Cell A1 has the following format (Multiple numbers in diff rows) 1234 23244 4434 121 1442 534 121223 12 How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434 (Cell C1) etc.. ? Thanks for your help. You can use a UDF to do this. To enter this <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then insert/module and paste the code below into the window that opens. Select your data range. Then <alt-F8 and RUN the Rearrange macro. As written, the numbers are returned as numeric values, so leading zeros will not be present. If you require preservation of leading zero's, then note the comment to change the format of the destination cells. Also note that if any of your Selected data range is in any of the destination cells, they will be overwritten and not recoverable. So backup your data. ================================================= Option Explicit Sub Rearrange() Dim c As Range Dim sTemp Dim i As Long For Each c In Selection sTemp = sTemp & c.Text & " " Next c sTemp = Application.WorksheetFunction.Trim(sTemp) sTemp = Split(sTemp, " ") Set c = Range("A1") For i = 0 To UBound(sTemp) With c(1, i + 1) .NumberFormat = "General" 'use numberformat @ for returning values as text '.NumberFormat = "@" .Value = sTemp(i) End With Next i End Sub ============================================ --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Wed, 26 Dec 2007 19:57:00 -0800, Jeff
wrote: Hi Ron, thanks for your response I tried your method below and the output as follow A B C D E F 1 1234 23244 121 1442 121223 12 4434 534 2 3 Where cell B2 and D2 still merge the numbers. Rgds..Jeff Then I didn't understand your question, and/or you didn't understand my response. My routine will not do that, if your data is as described. As a matter of fact, unless you have made some changes in what I've supplied, there is no way that it would even be writing anything in row 2. What changes did you make? Please post back with more details. Do you want to split the contents row by row? If so, just use the Data/Text to Columns wizard. If you want something else, you'll need to be more specific. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Wed, 26 Dec 2007 19:57:00 -0800, Jeff
wrote: Hi Ron, thanks for your response I tried your method below and the output as follow A B C D E F 1 1234 23244 121 1442 121223 12 4434 534 2 3 Where cell B2 and D2 still merge the numbers. Rgds..Jeff OK, I just read your response to Max and see that all of the data is in one cell -- not on individual rows as I had thought. Give me a minute. --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Wed, 26 Dec 2007 19:57:00 -0800, Jeff
wrote: Hi Ron, thanks for your response I tried your method below and the output as follow A B C D E F 1 1234 23244 121 1442 121223 12 4434 534 2 3 Where cell B2 and D2 still merge the numbers. Rgds..Jeff OK, this should work, now that I understand the data to be split is all in one cell. I didn't format the destination cell in this version, but that can be easily added depending on whether you want the values to be text or numeric. You can still use the Data/Text to columns wizard by specifying the delimiters as being <space and <other. In the <other box, hold down <alt while you type 010 on the NUMERIC KEYPAD (not on the numbers at the top of the keyboard). If that doesn't work, (and it might if there is something else funny about the data), you can try the sub below: ==================================== Option Explicit Sub Rearrange() Dim c As Range Dim re As Object, mc As Object Dim i As Long Const sPat As String = "\w+" Set re = CreateObject("vbscript.regexp") With re .Global = True .Pattern = sPat End With For Each c In Selection If re.test(c.Text) = True Then Set mc = re.Execute(c.Text) For i = 0 To mc.Count - 1 c.Offset(0, i).Value = mc(i) Next i End If Next c End Sub =================================== --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Give this macro a try...
Sub SplitCellText() Dim X As Long Dim Combo As String Dim Values() As String Combo = Replace(Replace(Range("A1").Value, vbCr, " "), vbLf, " ") Do While InStr(Combo, " ") Combo = Replace(Combo, " ", " ") Loop Values = Split(Trim(Combo)) For X = 0 To UBound(Values) Range("A1").Offset(0, X).Value = Values(X) Next End Sub Rick "Jeff" wrote in message ... Hi Max, thanks for your reply. The data is in cell A1 only. Not A1:A3. If using your method, original data 1234 23244 4434 121 1442 534 121223 12 will change to A B 1 1234 23244 where next row data 4434 121 1442 & 534 121223 122 will be gone. The output should be A B C D E F G H 1 1234 23244 4434 121 1442 534 121223 12 Rgds..Jeff "Max" wrote: One play which would bring you close .. Assuming the data as posted is in A1:A3 Select A1:A3, click Data Text to Columns, delimited. In step 2, check "Space" Finish. This splits the data into cols A to C. Then to extract the contents in A2:C3 into row 1, you could place this in D1: =OFFSET($A$2,INT((COLUMNS($A:A)-1)/3),MOD(COLUMNS($A:A)-1,3)) Copy D1 across to I1. Kill the formulas with an "in-place" copy n paste special as values. Clean up by clearing A2:C3. The "3" in the OFFSET formula refers to the number of source cols (in the above, its 3 cols - cols A to C). Adjust to suit. A better way might be to extract the split data in cols A to C down a col, instead of across a row. Eg you could place this instead in say E2: =OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) then copy E2 down as far as required to exhaust the source data, kill the formulas & clean up by using autofilter on col E, filter for zero, and delete all zero lines, remove autofilter. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeff" wrote: Hi, My excel file Cell A1 has the following format (Multiple numbers in diff rows) 1234 23244 4434 121 1442 534 121223 12 How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434 (Cell C1) etc.. ? Thanks for your help. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
2 superb subs for Jeff to use, one from you, Rick, and the other from Ron's
latest response. Just one question: Should the number of items split exceed the max number of cols, how could the results be snaked down to row 2 (& beyond)? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Thanks Rick. This macro is very good and meet my expectation.
"Rick Rothstein (MVP - VB)" wrote: Give this macro a try... Sub SplitCellText() Dim X As Long Dim Combo As String Dim Values() As String Combo = Replace(Replace(Range("A1").Value, vbCr, " "), vbLf, " ") Do While InStr(Combo, " ") Combo = Replace(Combo, " ", " ") Loop Values = Split(Trim(Combo)) For X = 0 To UBound(Values) Range("A1").Offset(0, X).Value = Values(X) Next End Sub Rick "Jeff" wrote in message ... Hi Max, thanks for your reply. The data is in cell A1 only. Not A1:A3. If using your method, original data 1234 23244 4434 121 1442 534 121223 12 will change to A B 1 1234 23244 where next row data 4434 121 1442 & 534 121223 122 will be gone. The output should be A B C D E F G H 1 1234 23244 4434 121 1442 534 121223 12 Rgds..Jeff "Max" wrote: One play which would bring you close .. Assuming the data as posted is in A1:A3 Select A1:A3, click Data Text to Columns, delimited. In step 2, check "Space" Finish. This splits the data into cols A to C. Then to extract the contents in A2:C3 into row 1, you could place this in D1: =OFFSET($A$2,INT((COLUMNS($A:A)-1)/3),MOD(COLUMNS($A:A)-1,3)) Copy D1 across to I1. Kill the formulas with an "in-place" copy n paste special as values. Clean up by clearing A2:C3. The "3" in the OFFSET formula refers to the number of source cols (in the above, its 3 cols - cols A to C). Adjust to suit. A better way might be to extract the split data in cols A to C down a col, instead of across a row. Eg you could place this instead in say E2: =OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) then copy E2 down as far as required to exhaust the source data, kill the formulas & clean up by using autofilter on col E, filter for zero, and delete all zero lines, remove autofilter. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeff" wrote: Hi, My excel file Cell A1 has the following format (Multiple numbers in diff rows) 1234 23244 4434 121 1442 534 121223 12 How do I split & re-arrange the data to 1234 (Cell A1) 23244 (Cell B1) 4434 (Cell C1) etc.. ? Thanks for your help. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Thanks Ron, macro is excellent & working well.
"Ron Rosenfeld" wrote: On Wed, 26 Dec 2007 19:57:00 -0800, Jeff wrote: Hi Ron, thanks for your response I tried your method below and the output as follow A B C D E F 1 1234 23244 121 1442 121223 12 4434 534 2 3 Where cell B2 and D2 still merge the numbers. Rgds..Jeff OK, this should work, now that I understand the data to be split is all in one cell. I didn't format the destination cell in this version, but that can be easily added depending on whether you want the values to be text or numeric. You can still use the Data/Text to columns wizard by specifying the delimiters as being <space and <other. In the <other box, hold down <alt while you type 010 on the NUMERIC KEYPAD (not on the numbers at the top of the keyboard). If that doesn't work, (and it might if there is something else funny about the data), you can try the sub below: ==================================== Option Explicit Sub Rearrange() Dim c As Range Dim re As Object, mc As Object Dim i As Long Const sPat As String = "\w+" Set re = CreateObject("vbscript.regexp") With re .Global = True .Pattern = sPat End With For Each c In Selection If re.test(c.Text) = True Then Set mc = re.Execute(c.Text) For i = 0 To mc.Count - 1 c.Offset(0, i).Value = mc(i) Next i End If Next c End Sub =================================== --ron |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Just one question: Should the number of items split exceed the max number
of cols, how could the results be snaked down to row 2 (& beyond)? For my approach, like this... Sub SplitCellText() Dim C As Long Dim R As Long Dim Combo As String Dim Values() As String Combo = Replace(Replace(Range("A1").Value, vbCr, " "), vbLf, " ") Do While InStr(Combo, " ") Combo = Replace(Combo, " ", " ") Loop Values = Split(Trim(Combo)) For C = 0 To UBound(Values) If C Mod Columns.Count = 0 And C 0 Then R = R + 1 Range("A1").Offset(R, C Mod Columns.Count).Value = Values(C) Next End Sub Rick |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Marvellous revision, Rick.
Thanks. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Thu, 27 Dec 2007 14:11:38 +0800, "Max" wrote:
2 superb subs for Jeff to use, one from you, Rick, and the other from Ron's latest response. Just one question: Should the number of items split exceed the max number of cols, how could the results be snaked down to row 2 (& beyond)? Just a matter of adjusting the Offset. For my routine, try this modification: ================================================== ======== Option Explicit Sub Rearrange() Dim c As Range Dim re As Object, mc As Object Dim i As Long Const sPat As String = "\w+" Dim ColCt As Long ColCt = Columns.Count Set re = CreateObject("vbscript.regexp") With re .Global = True .Pattern = sPat End With For Each c In Selection If re.test(c.Text) = True Then Set mc = re.Execute(c.Text) For i = 0 To mc.Count - 1 c.Offset(Int(i / ColCt), i Mod ColCt).Value = mc(i) Next i End If Next c End Sub ================================================== ================= --ron |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Thu, 27 Dec 2007 04:10:24 -0500, Ron Rosenfeld
wrote: On Thu, 27 Dec 2007 14:11:38 +0800, "Max" wrote: 2 superb subs for Jeff to use, one from you, Rick, and the other from Ron's latest response. Just one question: Should the number of items split exceed the max number of cols, how could the results be snaked down to row 2 (& beyond)? Just a matter of adjusting the Offset. For my routine, try this modification: ================================================= ========= Option Explicit Sub Rearrange() Dim c As Range Dim re As Object, mc As Object Dim i As Long Const sPat As String = "\w+" Dim ColCt As Long ColCt = Columns.Count Set re = CreateObject("vbscript.regexp") With re .Global = True .Pattern = sPat End With For Each c In Selection If re.test(c.Text) = True Then Set mc = re.Execute(c.Text) For i = 0 To mc.Count - 1 c.Offset(Int(i / ColCt), i Mod ColCt).Value = mc(i) Next i End If Next c End Sub ================================================= ================== --ron Hmmm. Some testing reveals an apparent limitation in the engine underlying my method. So it will not be able to handle data strings of the length you are considering. You should use Rick's method, instead. --ron |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Ron, thanks for posting your mod. Tried it out several times here
but it seems to terminate with the last result placed in HJ1? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Ron, thanks. Noted.
My earlier response crossed your follow up here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Thu, 27 Dec 2007 18:37:06 +0800, "Max" wrote:
Ron, thanks. Noted. My earlier response crossed your follow up here. Max, Further investigation reveals that the problem seems to be a 1024 character limitation in the .text property of the range object. Please try this routine instead, on your data, and let me know how it works. ================================================== == Sub Rearrange() Dim c As Range Dim re As Object, mc As Object Dim str As String Dim i As Long Const sPat As String = "\w+" Dim ColCt As Long ColCt = Columns.Count Set re = CreateObject("vbscript.regexp") With re .Global = True .Pattern = sPat End With For Each c In Selection str = c If re.test(str) = True Then Set mc = re.Execute(str) For i = 0 To mc.Count - 1 c.Offset(Int(i / ColCt), i Mod ColCt).Value = mc(i) Next i End If Next c End Sub ================================================== ======== --ron |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Ron, thanks. Noted.
My earlier response crossed your follow up here. Further investigation reveals that the problem seems to be a 1024 character limitation in the .text property of the range object. Please try this routine instead, on your data, and let me know how it works. ================================================== == Sub Rearrange() Dim c As Range Dim re As Object, mc As Object Dim str As String Dim i As Long Const sPat As String = "\w+" Dim ColCt As Long ColCt = Columns.Count Set re = CreateObject("vbscript.regexp") With re .Global = True .Pattern = sPat End With For Each c In Selection str = c If re.test(str) = True Then Set mc = re.Execute(str) For i = 0 To mc.Count - 1 c.Offset(Int(i / ColCt), i Mod ColCt).Value = mc(i) Next i End If Next c End Sub ================================================== ======== At the risk of sounding pedantic, I think your first statement in the For Each loop should more properly be this... str = c.Value But instead of doing this intermediate step of using a String variable, as above, to feed to the Execute property of your 're' object, you could have eliminated using this variable and done so directly (the way you did with the c.Text value originally) like this... Set mc = re.Execute(c.Value) ....that would have worked too, correct? Or will Execute only accept a typed String entity in order for it to work? Rick |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Thu, 27 Dec 2007 11:05:28 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Ron, thanks. Noted. My earlier response crossed your follow up here. Further investigation reveals that the problem seems to be a 1024 character limitation in the .text property of the range object. Please try this routine instead, on your data, and let me know how it works. ================================================== == Sub Rearrange() Dim c As Range Dim re As Object, mc As Object Dim str As String Dim i As Long Const sPat As String = "\w+" Dim ColCt As Long ColCt = Columns.Count Set re = CreateObject("vbscript.regexp") With re .Global = True .Pattern = sPat End With For Each c In Selection str = c If re.test(str) = True Then Set mc = re.Execute(str) For i = 0 To mc.Count - 1 c.Offset(Int(i / ColCt), i Mod ColCt).Value = mc(i) Next i End If Next c End Sub ================================================== ======== At the risk of sounding pedantic, I think your first statement in the For Each loop should more properly be this... str = c.Value But instead of doing this intermediate step of using a String variable, as above, to feed to the Execute property of your 're' object, you could have eliminated using this variable and done so directly (the way you did with the c.Text value originally) like this... Set mc = re.Execute(c.Value) ...that would have worked too, correct? Or will Execute only accept a typed String entity in order for it to work? Rick Actually, set mc = re.execute(c) would also work just fine. The "str" construct was left over from my testing to trying to figure out exactly where the limitation was, and I didn't clean things up afterwards. So far as c vs c.value, I believe they are equivalent, as I believe the value property is the default for the range property, although I would generally use c.value for clarity. As with the str construct, it was a leftover from my testing to figure out where the hangup was. I believe, although I have not found it documented (yet), that the TEXT property of the range function returns what is actually displayed in a cell, INCLUDING the limitation of 1024 characters that can be displayed. (I had not appreciated that 1024 limitation up until now). I had been in the habit of using the text property for regex stuff so as to preserve the formatting of numbers, which would be important in designing a regex. Obviously, for potentially long strings, this is inappropriate. By the way, in your routine, you could eliminate your IF line. (I know you like more compact routines :-)) If C Mod Columns.Count = 0 And C 0 Then R = R + 1 Range("A1").Offset(R, C Mod Columns.Count).Value = Values(C) to: Range("A1").Offset(Int(C / Columns.Count), _ C Mod Columns.Count).Value = Values(C) Best, --ron |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
See inline comments...
At the risk of sounding pedantic, I think your first statement in the For Each loop should more properly be this... str = c.Value But instead of doing this intermediate step of using a String variable, as above, to feed to the Execute property of your 're' object, you could have eliminated using this variable and done so directly (the way you did with the c.Text value originally) like this... Set mc = re.Execute(c.Value) ...that would have worked too, correct? Or will Execute only accept a typed String entity in order for it to work? Rick Actually, set mc = re.execute(c) would also work just fine. So far as c vs c.value, I believe they are equivalent, as I believe the value property is the default for the range property, although I would generally use c.value for clarity I just noticed I forgot to include why I posted my pedantic statement.. I do not like relying on default values without physically specifying them. As you state, clarity is the reason. When having to look at old code for maintenance or modification reasons, it is very easy to miss the fact that 'c' (or even a better, longer named object name) is not a variable, but rather an object reference with the default property assumed. I've tripped myself up over that one in the past when I thought taking this shortcut approach didn't matter. After wasting a considerable amount of time (in a code editing session some time back) tracking down a bug that would have been a lot more obvious if I had used the property name with the object, I'm now a strong (to the point of being obnoxious sometimes<g) advocate of never using default properties without physically specifying them. I believe, although I have not found it documented (yet), that the TEXT property of the range function returns what is actually displayed in a cell, INCLUDING the limitation of 1024 characters that can be displayed. (I had not appreciated that 1024 limitation up until now). This is good to know and be aware of. Thanks for noting it. As for it being documented, does a quote from Chip Pearson's website count? I had been in the habit of using the text property for regex stuff so as to preserve the formatting of numbers, which would be important in designing a regex. Obviously, for potentially long strings, this is inappropriate. By the way, in your routine, you could eliminate your IF line. (I know you like more compact routines :-)) If C Mod Columns.Count = 0 And C 0 Then R = R + 1 Range("A1").Offset(R, C Mod Columns.Count).Value = Values(C) to: Range("A1").Offset(Int(C / Columns.Count), _ C Mod Columns.Count).Value = Values(C) Best, --ron |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
** I hate the Ctrl-Enter keystroke which Sends the document in OE **
See inline comments... At the risk of sounding pedantic, I think your first statement in the For Each loop should more properly be this... str = c.Value But instead of doing this intermediate step of using a String variable, as above, to feed to the Execute property of your 're' object, you could have eliminated using this variable and done so directly (the way you did with the c.Text value originally) like this... Set mc = re.Execute(c.Value) ...that would have worked too, correct? Or will Execute only accept a typed String entity in order for it to work? Rick Actually, set mc = re.execute(c) would also work just fine. So far as c vs c.value, I believe they are equivalent, as I believe the value property is the default for the range property, although I would generally use c.value for clarity I just noticed I forgot to include why I posted my pedantic statement.. I do not like relying on default values without physically specifying them. As you state, clarity is the reason. When having to look at old code for maintenance or modification reasons, it is very easy to miss the fact that 'c' (or even a better, longer named object name) is not a variable, but rather an object reference with the default property assumed. I've tripped myself up over that one in the past when I thought taking this shortcut approach didn't matter. After wasting a considerable amount of time (in a code editing session some time back) tracking down a bug that would have been a lot more obvious if I had used the property name with the object, I'm now a strong (to the point of being obnoxious sometimes<g) advocate of never using default properties without physically specifying them. I believe, although I have not found it documented (yet), that the TEXT property of the range function returns what is actually displayed in a cell, INCLUDING the limitation of 1024 characters that can be displayed. (I had not appreciated that 1024 limitation up until now). This is good to know and be aware of. Thanks for noting it. As for it being documented, does a quote from Chip Pearson's website count? See the last line here... http://www.cpearson.com/excel/values.htm I had been in the habit of using the text property for regex stuff so as to preserve the formatting of numbers, which would be important in designing a regex. Obviously, for potentially long strings, this is inappropriate. By the way, in your routine, you could eliminate your IF line. (I know you like more compact routines :-)) If C Mod Columns.Count = 0 And C 0 Then R = R + 1 Range("A1").Offset(R, C Mod Columns.Count).Value = Values(C) to: Range("A1").Offset(Int(C / Columns.Count), _ C Mod Columns.Count).Value = Values(C) Yeah, I know... now! I use that trick all the time and can't think of a reason why I failed to see its use here. When I saw that you used it in your code, and then realized I had forgotten to do it that way, I figuratively kicked myself for having missed using it.<g Rick |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
Ron,
.. Please try this routine instead, on your data, and let me know how it works. Tested here -- your revised sub runs fine ! cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Fri, 28 Dec 2007 09:51:24 +0800, "Max" wrote:
Ron, .. Please try this routine instead, on your data, and let me know how it works. Tested here -- your revised sub runs fine ! cheers. Thanks for the feedback, Max. Best wishes, ron --ron |
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
On Thu, 27 Dec 2007 18:20:59 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: I believe, although I have not found it documented (yet), that the TEXT property of the range function returns what is actually displayed in a cell, INCLUDING the limitation of 1024 characters that can be displayed. (I had not appreciated that 1024 limitation up until now). This is good to know and be aware of. Thanks for noting it. As for it being documented, does a quote from Chip Pearson's website count? See the last line here... http://www.cpearson.com/excel/values.htm Oh yes, I would accept that. I never thought to look there for this piece of information. On the rest, I believe we are in violent agreement. It is exceedingly rare for me to not specify the property and rely on the default. But, I did do it when trying to figure out why my routine was not working on longer strings. Probably, I should stop doing it even then. --ron |
#27
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split & Rearrange number
I believe, although I have not found it documented (yet), that the TEXT
property of the range function returns what is actually displayed in a cell, INCLUDING the limitation of 1024 characters that can be displayed. (I had not appreciated that 1024 limitation up until now). This is good to know and be aware of. Thanks for noting it. As for it being documented, does a quote from Chip Pearson's website count? See the last line here... http://www.cpearson.com/excel/values.htm Oh yes, I would accept that. I never thought to look there for this piece of information. On the rest, I believe we are in violent agreement. LOL ... Violent agreement... I like that phrasing. It is exceedingly rare for me to not specify the property and rely on the default. But, I did do it when trying to figure out why my routine was not working on longer strings. Probably, I should stop doing it even then. When coding (either compiled VB or Excel VBA), I always specifically add the default property reference, even in throwaway code (force of habit after all these years I guess)... most of the time, Intellisense will help me out. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split a number in a cell into parts | Excel Discussion (Misc queries) | |||
Split or delete part of a number | Excel Discussion (Misc queries) | |||
help me on rearrange cells based on its numerical values without repeating any number | Excel Worksheet Functions | |||
Split text and number | Excel Discussion (Misc queries) | |||
Letter/Number Split | Excel Worksheet Functions |