Splitting String into Consitiutent Parts including spaces characte

[Guest]

I have a string which has had elements removed creating variable spaces (have
shown spaces with dashes):

-$A7-$B$11-----$I$2-$I$6--I6--------1-3-3-$I$6-K$3

I want to be able to split this string into its consituent parts (spaces
included). I am not sure if using the split function will work as I am not
using the spaces as delimiters. I want to be able to split as follows:

Substring1 = -
Substring2 = $A7
Substring3 = -
Substring4 = B$11
Substring5 = -
Substring6 = -
Substring7 = -
Substring8 = -
Substring9 = -
Substring9 = $I$2
etc......

Can anyone tell me what other options I have for this besides Split? Or if
Split can in fact do this.

Thanks

 

[Guest]

Have you tried it? I copied your string to the immediate pane and replaced
"-" with " " and tried a = split(z, " ") and the results appear to be what
you specified (except array is starting at 0 instead of 1.)

 

[Norman Jones]

Hi ExcelMonkey,

Try:
'=============>>
Public Sub Tester()
Dim sStr As String
Dim arr As Variant
Dim i As Long, j As Long

sStr = _
"-$A7-$B$11-----$I$2-$I$6--I6--------1-3-3-$I$6-K$3"

arr = Split(Replace(sStr, "-", "#-#"), "#")

For i = LBound(arr) To UBound(arr)
If arr(i) <> vbNullString Then
j = j + 1
Cells(j, "A").Value = arr(i)
End If
Next i
End Sub
'<<=============

 

[Ron Rosenfeld]

I have a string which has had elements removed creating variable spaces (have
shown spaces with dashes):

-$A7-$B$11-----$I$2-$I$6--I6--------1-3-3-$I$6-K$3

I want to be able to split this string into its consituent parts (spaces
included). I am not sure if using the split function will work as I am not
using the spaces as delimiters. I want to be able to split as follows:

Substring1 = -
Substring2 = $A7
Substring3 = -
Substring4 = B$11
Substring5 = -
Substring6 = -
Substring7 = -
Substring8 = -
Substring9 = -
Substring9 = $I$2
etc......

Can anyone tell me what other options I have for this besides Split? Or if
Split can in fact do this.

Thanks

If you just use Split, I believe that the elements of the array that are
<space>'s will be null strings. So if you want <space>'s there, you could
substitute.

For example:

=======================
a = Split(your_string)
For i = 0 To UBound(a)
If a(i) = "" Then a(i) = " "
Next i
======================


--ron

 

[NickHK]

ExcelMonkey,
I assume each "-" or range/number is an element, all of which need to be
preserved, for a total of 32 elements.

Would "-$I$2-$I$6I6-" be a valid input ? Note 3 non-space values; $I$2,
$I$6I ,6.
Or "-3-3$I$6-" ?

NickHK

 

[Guest]

So I am confused. I effectively have this string:

$A7 $B$11 $I$2 $I$6 I6 1 3 3 $I$6 K$3

It clearly has spaces in it at variable lenghts. I want to split it up into
segments. Then I want to step throught the array, if the substring is a cell
address, I want to offset by 1 column if its not a cell address (a number or
a space) I want to make it a space. The problem I am having is that when I
step throught the array, I am getting space values for spaces that are "" and
" ". I am not sure why this is. My goal was to split the string by each
into its consitutent substrings including spaces (i.e 0 = " ", 1 = $A7, 2 =
$B$11, 3 = " " etc). The code fails on the line Set r = Range(Suspectedrng)
because some of the array elements return values of "". Why is this?

Sub Thing ()
Dim CurrentFormula As String
Dim SuspectedrngArray As Variant
Dim X As Integer
Dim r As Range
Dim RevisedRngRight As String
Dim PassedRange As String

CurrentFormula = $A7 $B$11 $I$2 $I$6 I6 1 3 3 $I$6 K$3
SuspectedrngArray = Split(Replace(CurrentFormula, " ", "# #"), "#")

For X = LBound(SuspectedrngArray) To UBound(SuspectedrngArray)
Suspectedrng = SuspectedrngArray(X)
'If cell address then offset otherwise, make " "
If Not Suspectedrng = " " And Not
Application.WorksheetFunction.IsNumber(Suspectedrng) Then
Set r = Range(Suspectedrng)
RevisedRngRight = r.Offset(0, 1).Address
ElseIf RevisedRngRight = " " Then
RevisedRngRight = " "
Else
RevisedRngRight = Suspectedrng
End If
PassedRange = PassedRange & RevisedRngRight
Next

End Sub

 

[Norman Jones]

Hi ExcelMonkey,

Try replacing:

If Not Suspectedrng = " " And Not
Application.WorksheetFunction.IsNumber(Suspectedrng) Then

with:

If Not Suspectedrng = " " _
And Not Suspectedrng = vbNullString _
And Not Application.IsNumber(Suspectedrng) Then

 

[Guest]

The back end of the array has numbers(1,3,3):
$A7 $B$11 $I$2 $I$6 I6 1 3 3 $I$6 K$3

As these are text characters, they do not return TRUE in the:
ISNUMBER("1"):

?Application.WorksheetFunction.IsNumber("1")
False

vs.

?Application.WorksheetFunction.IsNumber(1)
True

Is there a function I can wrap around these to turn them into numbers?

Thanks

EM

 

[Guest]

I can conver the text value of "1" to a number by using the Cdbl() function
doing the following:

?Application.WorksheetFunction.IsNumber(Cdbl("1"))
True

However this may need som error handling as the following will give me an
error:
?Application.WorksheetFunction.IsNumber(Cdbl("$A$1"))

 

[Norman Jones]

Hi ExcelMonkey

?Application.WorksheetFunction.IsNumber(CLng("1"))
True

 

[Guest]

Sorry I have tried to post this 3 times now but the site keeps crashing. How
do I implemen the error handling need for non numbers (range addresses and
spaces):

?Application.WorksheetFunction.IsNumber(CLng("$A$1"))
?Application.WorksheetFunction.IsNumber(CLng(" "))

Thanks

EM

 

[Norman Jones]

Hi ExcelMonkey,

Try instead:

If Not Suspectedrng = " " _
And Not Suspectedrng = vbNullString _
And NotIsNumeric(Suspectedrng) Then


?Application.WorksheetFunction.IsNumber("1")
False

?Isnumeric("1")
True