please help

[sunypack]

Would anyone please help me write a VBA progam to do the following:
A cell (e.g. Cell(A1)) contains a string; alphet, 12.3, 23.4, 56.7
Break the string and put each of them in separate cells (e.g.
alphbet in Cell(B2), 12.3 in Cell(C2),
23.4 in Cell(D2), and 56.7 in Cell(E2)).
Thank you,

 

[Guest]

Sub sunny()
s = Split(Range("A1").Value, ",")
For i = 0 To UBound(s)
Cells(2, i + 2).Value = s(i)
Next
End Sub

 

[John Coleman]

Try

Sub SplitString()
Dim A As Variant, n As Long
Dim R1 As Range
Dim r2 As Range

Set R1 = Application.InputBox _
(Prompt:="Select cell containing input", Type:=8)
A = Split(R1.Text, ",")
n = UBound(A)
Set r2 = Application.InputBox _
(Prompt:="Select cell to start output in", Type:=8)
Set r2 = Range(r2, r2.Offset(0, n))
r2.Value = A
End Sub


Then - if in the first input box you select A1 and in the second you
select B2, you get exactly the output you desire. It would also be
possible to hardwire some of the input/output cell choices, or make
them parameters passed to the sub.

HTH

-John Coleman

 

[John Coleman]

A slight refinement, if you want to eliminate any leading or trailing
white spaces in the output, use:

Sub SplitString()
Dim A As Variant, n As Long, i As Long
Dim R1 As Range
Dim R2 As Range

Set R1 = Application.InputBox _
(Prompt:="Select cell containing input", Type:=8)
A = Split(R1.Text, ",")
n = UBound(A)
For i = 0 To n
A(i) = Trim(A(i))
Next i
Set R2 = Application.InputBox _
(Prompt:="Select cell to start output in", Type:=8)
Set R2 = Range(R2, R2.Offset(0, n))
R2.Value = A
End Sub


I should have thought of that the first time. Extraneous white spaces
can be annoying.

 

[Joerg]

Why would you need VBA? You could use standard Excel funcionality:
Go to Data = > Text to Columns and define comma as your delimiter.

Joerg

 

[sunypack]

Although i have used excel quite a bit, i have very limitted knowledge
about the functions. Similarly, i used macro by automaticaly recording
way back when but not at all vba programing. i would like to
manipulate internet-downloaded data where the split of strings into
separate cells is needed. i am having fun with code given by Mr.
Coleman. I am having a compile syntex error. Maybe my excel version is
not compatlbe.
Appreciate all the help i am getting.

 

[John Coleman]

What sort of syntax error? I just copy-pasted the code from Google
(which I use for my newsgroup interface) to a new workbook and it
worked right away. Sometimes, depending on your browser and window
size, etc., line breaks will be introduced in the copy-paste step that
weren't in the original code. If you paste code into a code window and
see syntax errors on 2 consecutive lines, try combining them into 1
line. I would be surprised if any version compatibility issues were
involved.

HTH

-John Coleman

 

[Joerg]

You don't need any function. What I meant with functionality: You can simply
use the menu and choose Data = > Text to Columns.

As far as I can see Excel provides exactly what you need "right out of the
box", so why do you insist on using macros? I don't want to discourage you
from using (and understanding) macros, but why bother if you don't need one?

Joerg