Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- Gary's Student gsnu200702 " 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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, 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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. On Jan 28, 11:01 am, "John Coleman" wrote: 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, 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,- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
Why would you need VBA? You could use standard Excel funcionality:
Go to Data = Text to Columns and define comma as your delimiter. Joerg wrote in message oups.com... 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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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. On Jan 28, 6:44 pm, "Joerg" wrote: Why would you need VBA? You could use standard Excel funcionality: Go to Data = Text to Columns and define comma as your delimiter. Joerg wrote in ooglegroups.com... 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,- Hide quoted text -- Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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, wrote: 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. On Jan 28, 6:44 pm, "Joerg" wrote: Why would you need VBA? You could use standard Excel funcionality: Go to Data = Text to Columns and define comma as your delimiter. Joerg wrote in ooglegroups.com... 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,- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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 wrote in message ups.com... 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. On Jan 28, 6:44 pm, "Joerg" wrote: Why would you need VBA? You could use standard Excel funcionality: Go to Data = Text to Columns and define comma as your delimiter. Joerg wrote in ooglegroups.com... 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,- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|