please help

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

Signature

Gary's Student
gsnu200702

> 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,

Reply to this Message

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

On Jan 28, 10:48 am, sunyp...@yahoo.com wrote:

> 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,

Reply to this Message

John Coleman - 28 Jan 2007 16:07 GMT

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.

> Try
>
[quoted text clipped - 30 lines]
> > 23.4 in Cell(D2), and 56.7 in Cell(E2)).
> > Thank you,- Hide quoted text -- Show quoted text -

Reply to this Message

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

Joerg

> 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,

Reply to this Message

sunypack@yahoo.com - 29 Jan 2007 06:01 GMT

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.

> Why would you need VBA? You could use standard Excel funcionality:
> Go to Data = > Text to Columns and define comma as your delimiter.
[quoted text clipped - 9 lines]
> > 23.4 in Cell(D2), and 56.7 in Cell(E2)).
> > Thank you,- Hide quoted text -- Show quoted text -

Reply to this Message

John Coleman - 29 Jan 2007 07:03 GMT

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

On Jan 29, 1:01 am, sunyp...@yahoo.com wrote:

> Although i have used excel quite a bit, i have very limitted knowledge
> about the functions. Similarly, i used macro by automaticaly recording
[quoted text clipped - 18 lines]
> > > 23.4 in Cell(D2), and 56.7 in Cell(E2)).
> > > Thank you,- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

Reply to this Message

Joerg - 29 Jan 2007 09:18 GMT

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

> Although i have used excel quite a bit, i have very limitted knowledge
> about the functions. Similarly, i used macro by automaticaly recording
[quoted text clipped - 18 lines]
> > > 23.4 in Cell(D2), and 56.7 in Cell(E2)).
> > > Thank you,- Hide quoted text -- Show quoted text -

Reply to this Message