Convert String of 512 numbers to a range
 

Hello,

How would I convert a single cell string consiting of 512 values: 545, 565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

Hi Kevin
I would use 'Data - Text to columns'. Choose fixed lenght though this
will be quite an effort to choose the delimiting positions for 512
characters :-).
You can also enter the following array formula:
=MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)
select the target rows, enter this formula and hit CTRL+SHIFT+ENTER
(assumption: A1 stores your string)

HTH
Frank

Kevin G wrote:
Hello,

How would I convert a single cell string consiting of 512 values:
545, 565, 576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

I think he said 512 values, and showed 3 digit values separated by commas.
With only 256 columns, he would have to at least break the string in half as
a first step to using Data - Text to Columns.



--
Regards,
Tom Ogilvy

Frank Kabel wrote in message
...
Hi Kevin
I would use 'Data - Text to columns'. Choose fixed lenght though this
will be quite an effort to choose the delimiting positions for 512
characters :-).
You can also enter the following array formula:
=MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)
select the target rows, enter this formula and hit CTRL+SHIFT+ENTER
(assumption: A1 stores your string)

HTH
Frank

Kevin G wrote:
Hello,

How would I convert a single cell string consiting of 512 values:
545, 565, 576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

Kevin,

This converts to rows as columns max out at 256
First bit puts a string together, second bit strips it apart.

Sub test()
Const cSeparator = ","
Dim str512 As String, i As Long, j As Long

'build it
str512 = ""
For i = 0 To 512 - 1
str512 = str512 & IIf(str512 = "", "", cSepChar & " ") & Int(Rnd() *
900 + 100)
Next

'strip it
With Sheet1
i = 1: j = InStr(1, str512, cSeparator)
Do Until j = 0
.Cells(i, 1) = Trim(Mid(str512, 1, j - 1))
str512 = Mid(str512, j + Len(cSeparator))
i = i + 1
j = InStr(1, str512, cSeparator)
Loop
If Len(Trim(str512)) 0 Then Cells(i, 1) = Trim(str512)
End With
End Sub


Rob


"Kevin G" wrote in message
news:v0ZRb.29750$A7.8532@edtnps84...
Hello,

How would I convert a single cell string consiting of 512 values: 545,
565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

Hi Tom
you're right (i forgot that he had 3 digit values). He should change
the formula to
=MID(A1,1+3*(ROW(INDIRECT("1:" & LEN(A1)))-1),3)
To enter this first select sufficient rows (512) and enter this formula
as array formula. As Tom pointed out the OP has to use rows as the
maximum columns are 256.

Frank


Tom Ogilvy wrote:
I think he said 512 values, and showed 3 digit values separated by
commas. With only 256 columns, he would have to at least break the
string in half as a first step to using Data - Text to Columns.




Frank Kabel wrote in message
...
Hi Kevin
I would use 'Data - Text to columns'. Choose fixed lenght though
this
will be quite an effort to choose the delimiting positions for 512
characters :-).
You can also enter the following array formula:
=MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)
select the target rows, enter this formula and hit CTRL+SHIFT+ENTER
(assumption: A1 stores your string)

HTH
Frank

Kevin G wrote:
Hello,

How would I convert a single cell string consiting of 512 values:
545, 565, 576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

How about transposing this.

"Rob van Gelder" wrote in message
...
Kevin,

This converts to rows as columns max out at 256
First bit puts a string together, second bit strips it apart.

Sub test()
Const cSeparator = ","
Dim str512 As String, i As Long, j As Long

'build it
str512 = ""
For i = 0 To 512 - 1
str512 = str512 & IIf(str512 = "", "", cSepChar & " ") & Int(Rnd()
*
900 + 100)
Next

'strip it
With Sheet1
i = 1: j = InStr(1, str512, cSeparator)
Do Until j = 0
.Cells(i, 1) = Trim(Mid(str512, 1, j - 1))
str512 = Mid(str512, j + Len(cSeparator))
i = i + 1
j = InStr(1, str512, cSeparator)
Loop
If Len(Trim(str512)) 0 Then Cells(i, 1) = Trim(str512)
End With
End Sub


Rob


"Kevin G" wrote in message
news:v0ZRb.29750$A7.8532@edtnps84...
Hello,

How would I convert a single cell string consiting of 512 values: 545,
565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

How would you transpose a single cell?

Think not.

--
Regards,
Tom Ogilvy

"Kevin G" wrote in message
news:AJ9Sb.38990$oj2.26828@edtnps89...
How about transposing this.

"Rob van Gelder" wrote in message
...
Kevin,

This converts to rows as columns max out at 256
First bit puts a string together, second bit strips it apart.

Sub test()
Const cSeparator = ","
Dim str512 As String, i As Long, j As Long

'build it
str512 = ""
For i = 0 To 512 - 1
str512 = str512 & IIf(str512 = "", "", cSepChar & " ") &
Int(Rnd()
*
900 + 100)
Next

'strip it
With Sheet1
i = 1: j = InStr(1, str512, cSeparator)
Do Until j = 0
.Cells(i, 1) = Trim(Mid(str512, 1, j - 1))
str512 = Mid(str512, j + Len(cSeparator))
i = i + 1
j = InStr(1, str512, cSeparator)
Loop
If Len(Trim(str512)) 0 Then Cells(i, 1) = Trim(str512)
End With
End Sub


Rob


"Kevin G" wrote in message
news:v0ZRb.29750$A7.8532@edtnps84...
Hello,

How would I convert a single cell string consiting of 512 values: 545,
565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

If it's not something that has to be in a macro, what about Data: Text to
Columns:... ? or if it's in a macro, use range.TextToColumns

Although the whole "columns max out at 256" will require a solution.

but you could always use range.texttocolumns with fixed width, split the
cell into 2 or 4 or something, copy the chunks you've split off into the
next row (range.offset(1,0)=range.offset(0,1),
range.offset(2,0)=range.offset(0,2) etc) then use range.texttocolumns on
each of your new cells.


--

__________________________________________________ __________________________
________________
Please reply to newsgroup so everyone can benefit.
Email address is not valid (see sparkingwire.com)
__________________________________________________ __________________________
________________
"Kevin G" wrote in message
news:AJ9Sb.38990$oj2.26828@edtnps89...
How about transposing this.

"Rob van Gelder" wrote in message
...
Kevin,

This converts to rows as columns max out at 256
First bit puts a string together, second bit strips it apart.

Sub test()
Const cSeparator = ","
Dim str512 As String, i As Long, j As Long

'build it
str512 = ""
For i = 0 To 512 - 1
str512 = str512 & IIf(str512 = "", "", cSepChar & " ") &
Int(Rnd()
*
900 + 100)
Next

'strip it
With Sheet1
i = 1: j = InStr(1, str512, cSeparator)
Do Until j = 0
.Cells(i, 1) = Trim(Mid(str512, 1, j - 1))
str512 = Mid(str512, j + Len(cSeparator))
i = i + 1
j = InStr(1, str512, cSeparator)
Loop
If Len(Trim(str512)) 0 Then Cells(i, 1) = Trim(str512)
End With
End Sub


Rob


"Kevin G" wrote in message
news:v0ZRb.29750$A7.8532@edtnps84...
Hello,

How would I convert a single cell string consiting of 512 values: 545,
565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

Kevin,

You can span across columns. If you keep within the 256 column limit.
Or you can span across sheets as well, as in this example:

Sub test()
Const cSeparator = ","
Dim str512 As String, i As Long, j As Long

'build it
str512 = ""
For i = 0 To 512 - 1
str512 = str512 & IIf(str512 = "", "", cSeparator & " ") & Int(Rnd()
* 900 + 100)
Next

'strip it
On Error GoTo e
i = 0: j = InStr(1, str512, cSeparator)
Do Until j = 0
Worksheets(i \ 256 + 1).Cells(1, i Mod 256 + 1).Value =
Trim(Mid(str512, 1, j - 1))
str512 = Mid(str512, j + Len(cSeparator))
i = i + 1
j = InStr(1, str512, cSeparator)
Loop
If Len(Trim(str512)) 0 Then Worksheets(i \ 256 + 1).Cells(1, i Mod 256
+ 1).Value = Trim(str512)
Exit Sub

e: Worksheets.Add after:=Worksheets(Worksheets.Count)
Resume
End Sub

Rob


"Kevin G" wrote in message
news:AJ9Sb.38990$oj2.26828@edtnps89...
How about transposing this.

"Rob van Gelder" wrote in message
...
Kevin,

This converts to rows as columns max out at 256
First bit puts a string together, second bit strips it apart.

Sub test()
Const cSeparator = ","
Dim str512 As String, i As Long, j As Long

'build it
str512 = ""
For i = 0 To 512 - 1
str512 = str512 & IIf(str512 = "", "", cSepChar & " ") &
Int(Rnd()
*
900 + 100)
Next

'strip it
With Sheet1
i = 1: j = InStr(1, str512, cSeparator)
Do Until j = 0
.Cells(i, 1) = Trim(Mid(str512, 1, j - 1))
str512 = Mid(str512, j + Len(cSeparator))
i = i + 1
j = InStr(1, str512, cSeparator)
Loop
If Len(Trim(str512)) 0 Then Cells(i, 1) = Trim(str512)
End With
End Sub


Rob


"Kevin G" wrote in message
news:v0ZRb.29750$A7.8532@edtnps84...
Hello,

How would I convert a single cell string consiting of 512 values: 545,
565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

Just a different idea using text to columns. It splits the string onto
A1:A2, then uses text to column.

Sub Demo()
'// Dana DeLouis
Dim s As String
Dim half As Long
Dim v As Variant

s = [A1]
half = ((Len(s) - Len(Replace(s, ",", vbNullString))) \ 2) + 1
[A1:A2].NumberFormat = "@"
v = Split(Replace(Replace(s, ",", ";", 1, half), ";", ",", 1, half - 1),
";")
[A1] = v(0)
[A2] = v(1)

Range("A1:A2").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False

End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Kevin G" wrote in message
news:v0ZRb.29750$A7.8532@edtnps84...
Hello,

How would I convert a single cell string consiting of 512 values: 545,
565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham

Convert String of 512 numbers to a range
 

Just another non-looping technique if you wish to use Regular Expressions.
It would be easy to adjust it for larger values. This assumes data is in
A1, and you wish to parse it to A1:IV2


Sub Demo()
'// = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Library Reference:
'// Microsoft VBScript Regular Expressions 5.5
'// = = = = = = = = = = = = = = = = = = = = = =

Dim s As String
Dim Matches As MatchCollection

s = [A1]
[A1:A2].NumberFormat = "@"

With New RegExp
.Global = True
.Pattern = ","
Set Matches = .Execute(s)
[A1] = Left$(s, Matches(Matches.Count \ 2).FirstIndex)
[A2] = Mid$(s, Matches(Matches.Count \ 2).FirstIndex + 2)
End With

Range("A1:A2").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Space:=False, _
Other:=False, _
Comma:=True
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dana DeLouis" wrote in message
...
Just a different idea using text to columns. It splits the string onto
A1:A2, then uses text to column.

Sub Demo()
'// Dana DeLouis
Dim s As String
Dim half As Long
Dim v As Variant

s = [A1]
half = ((Len(s) - Len(Replace(s, ",", vbNullString))) \ 2) + 1
[A1:A2].NumberFormat = "@"
v = Split(Replace(Replace(s, ",", ";", 1, half), ";", ",", 1, half - 1),
";")
[A1] = v(0)
[A2] = v(1)

Range("A1:A2").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False

End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Kevin G" wrote in message
news:v0ZRb.29750$A7.8532@edtnps84...
Hello,

How would I convert a single cell string consiting of 512 values: 545,
565,
576, etc... to a range of 512 cells.

Thanks, Kevin Graham