Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 921
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 921
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 921
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 921
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 921
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Split & Rearrange number

Marvellous revision, Rick.
Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Split & Rearrange number

Ron, thanks. Noted.
My earlier response crossed your follow up here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Split a number in a cell into parts okanem Excel Discussion (Misc queries) 4 August 21st 06 03:59 PM
Split or delete part of a number Oystein Excel Discussion (Misc queries) 4 March 6th 06 07:37 PM
help me on rearrange cells based on its numerical values without repeating any number amrezzat Excel Worksheet Functions 0 November 22nd 05 12:01 AM
Split text and number Jambruins Excel Discussion (Misc queries) 5 October 5th 05 09:56 PM
Letter/Number Split carlito_1985 Excel Worksheet Functions 3 June 25th 05 02:28 AM


All times are GMT +1. The time now is 05:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"