Solved

Split String to Array

Posted on 2015-01-02
5
176 Views
Last Modified: 2015-01-02
How to write a VBA to string a string into chars ?

Input string :
   17DFA0.08 18DFB0.23 92XGG1.49 1XRH0.09 1XGT1.34

Output array :
17DFA0.08
18DFB0.23
92XGG1.49
1XRH0.09
1XGT1.34

Tks
0
Comment
Question by:AXISHK
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527389
Where do you want it splitting into? Into an array, or into cells?
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 150 total points
ID: 40527392
Sub SplitExample()
Dim arr() As String
Dim x As Integer
inputstring = "17DFA0.08 18DFB0.23 92XGG1.49 1XRH0.09 1XGT1.34"
arr = Split(inputstring, " ")
For x = LBound(arr) To UBound(arr)
    Debug.Print arr(x)
Next

Set wb = Workbooks.Add
With wb.Worksheets(1)
    For x = LBound(arr) To UBound(arr)
        .Cells(x + 1, 1) = arr(x)
    Next
End With

End Sub
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40527916
if you just need the data put into a cell, you might be able to use something like this:
a="17DFA0.08 18DFB0.23 92XGG1.49 1XRH0.09 1XGT1.34"
activecell.value = replace(a," ",vbcrlf)

Open in new window

0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 150 total points
ID: 40528041
Dim strParts() As String

strParts = Split(YourString, " ")

Open in new window

strParts(0) will be 17DFA0.08 and strParts(1) will be 18DFB0.23, etc.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 200 total points
ID: 40528052
You can also populate the parsed/split data into multiple cells as follows:
a="17DFA0.08 18DFB0.23 92XGG1.49 1XRH0.09 1XGT1.34"
vector =  Split(a, " ")
activesheet.range(activecell,activecell.Offset(ubound(vector))).Value=worksheetfunction.transpose(vector)

Open in new window

I describe these and other techniques in my Fast Data Push to Excel article:
http:A_2253.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now