String Split on Line Break

Question

I have a textbox on a MS Access form that users are going to copy a column of numbers into from an excel spreadsheet. I need to take this input and use it as parameters to build a query. I have code that looks like this

Dim data as variant
Dim input as String
data = Split(input,vbLf)

I want to be able to build a list of the input from the users but I can't figure out how to split it on the line break. I've tried "\n\r", "\n". "\r", vbCrLf, vbLf. The input looks like "12345[][]23456" with the box characters between each number

Thanks

If your data comes from a different OS, vbLf might be appropriate. I forget which is which, but I think Unix uses a linefeed alone and maybe Mac uses a carriage return alone. Windows users carriage return followed by linefeed. If you ever need to use data from other OS's you'll have to look it up! :) — David-W-Fenton, Sep 18 '09 at 01:25

score 13 · Accepted Answer · answered Sep 17 '09 at 15:09

13

I got Split to work for me using vbCrLf. I also wrote the result of Split to a String array.

Here's my code:

Dim data() As String
Dim yourInput As String
data = Split(yourInput, vbCrLf)

answered Sep 17 '09 at 15:09

Jay Riggs

53,046
9
139
151

I tested this out as well with a variant and it worked fine using vbCrLf. – Buggabill Sep 17 '09 at 15:13

score 3 · Answer 2 · answered Sep 17 '09 at 15:10

3

vbCRLF worked for me, try: Strings.Chr(13) & Strings.Chr(10) (which is vbCRLF)

try to see what is the ASCII code of those 2 boxes:

    //ex for input = "12345[][]23456"
    Strings.Asc(Strings.Mid(input, 6, 1)) 
    Strings.Asc(Strings.Mid(input, 7, 1))

answered Sep 17 '09 at 15:10

manji

47,442
5
96
103

Strings.Chr(10) for vbLf and Strings.Chr(13) for vbCRLF – kta Dec 20 '11 at 05:09

String Split on Line Break

2 Answers2

Linked