Convert String of 512 numbers to a range

  • Thread starter Thread starter Kevin G
  • Start date Start date
K

Kevin G

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
 
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
 
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.
 
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
 
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
 
How about transposing this.

Rob van Gelder said:
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
Click to expand...
 
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,

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top