VBA problem SPLITting an array with hyphen as delimiter

[Andyatwork]

Hello all,

I am having a problem passing a text string (from a csv file pulled into excel 07) to an array and using split to pull out some relevant information.

The string is always of the format; Closure : 1234 - blah blah blah where '1234' is a variable number sandwiched between a colon and a hyphen, and 'blah blah blah' is a free text field populated by a user at some earlier point in time.

I want to split the string on the Colon, discard the first element (Closure), pass the second element to a new array, split the new array on the Hyphen and push the resulting new first element (the number) to a range, and the second element (the free text string) to a second range.

My problem is that after doing the first split on the colon my first array seems to think it only has 2 elements; "Closure" and "1234". Everything from the hyphen on goes poof so of course the second split does nothing. I've confirmed this with debug.print.

I have read that hyphens can sometimes be represented by ascii chr(45) and chr(150) but replacing "-" with "chr(45)" or "chr(150)" as the delimiter optional in SPLIT made no difference.

Sample string to be split
Closure: 616 - Rebatch Sale 30/06/2014

End result should be 616 in every cell of a predefined range and "Rebatch Sale 30/06/2014" in every cell of a second predefined range.

Has anyone got any tips on how I can salvage the free text field from this string? VBA just isn't seeing the hyphen or registering the existence of anything past it.

I think I need to identify the hyphen and replace it with something that VBA can see, and hopefully the invisibility cloak will go away and SPLIT should work again.

Any guidance or pointing out how i'm using split and array incorrectly, would be greatfully received.

Regards,

Andy

 

[TheGlovner]

Would something more like this work?

Sub test()

sStringInput = "Closure: 616 - Rebatch Sale 30/06/2014"
iColonLoc = InStr(1, sStringInput, ":") + 1
iHyphenLoc = InStr(1, sStringInput, "-")
iValue = CInt(Trim(Mid(sStringInput, iColonLoc, iHyphenLoc - iColonLoc)))
sFreeText = Trim(Mid(sStringInput, iHyphenLoc + 1, Len(sStringInput)))

End Sub

Seems to split the two values into separate variables for me, you could then pass these into your array elements?

 

[Andyatwork]

I don't think so. The problem is that vba simply cannot see the hyphen in my string so

iHyphenLoc = InStr(1, sStringInput, "-")

would fall over the same way my original attempt did.
When I debugged my first attempt;

arrCls() = Split(strCls, ":")
debug.print "arrcls(0) is :" & arrcls(0) & ". arrcls(1) is : " & arrcls(1)

it would return "Closure" for arrcls(0) but arrcls(1) was just the number. I'm was expecting to see arrcls(1) return as "616 - Rebatch for etc etc". All i actually got was "616".The hyphen and everything after it was simply not there.

So i'm thinking that there is some interaction with forbidden characters in VBA that I am too newb to fully understand.
I think the particular breed of hyphen that my csv is using is causing problems.

I take it there isn't an implied delimiter in split that takes effect even if you specifiy a different character? My text string wouldn't have been squirted off to a third or fourth element I am unware of would it?

 

[Andyatwork]

ok, i've just double checked my debug code as i was writing that from memory and it is now recognising the hyphen.

I think I might be going mad. It definitely wasn't registering the other day. Back to the coding board I go.

Thank you for the reply Glovner, it seems to have unblocked something.

Andy

 

[Andyatwork]

In case anyone is interested in the code that I finally managed to get work. I figure I had a stupid syntax error somewhere else and the problem was nothing to do with hypens and everything to do with me staring at the same code for too long.

'   Extract closure from header row, insert as column and delete 2 header rows
    Columns("A:B").Insert shift:=xlToRight
    Range("A3:b3") = Array("Closure", "Closure name")
    Dim strCls As String
    Dim arrCls() As String
    Dim rngCls As Range
    Set rngCls = Range("c2")
'   set string to original report value
    strCls = rngCls.Value
'   split string and feed back to array
    arrCls = Split(strCls, ":")
    arrCls = Split(arrCls(1), "-")
                   
    With tempsheet
        lrow = .Cells(.Rows.Count, "C").End(xlUp).Row
        Range("A4:A" & lrow).Value = arrCls(0)
        Range("b4:b" & lrow).Value = arrCls(1)
        Range("A1:A2").EntireRow.Delete
        lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    
    Set rngCopy = tempsheet.Range(Cells(2, 1), Cells(lrow, lcol))
    rngCopy.Copy
    shExt.Range("A" & elrow + 1).PasteSpecial xlPasteValues
    tempsheet.Delete