CODEWITHSUNDEEP
excelvba
user7261203
user7261203

Reputation:

Use an Excel cell like an array

I have an Excel file and I have to count the number of "." for each cell. One cell has some characters (for example 'A01.10.10.10') Is it possible transfer into an array the content of a single cell?

Upvotes: 1

Views: 86

Answers (3)

Tom
Tom

Reputation: 9878

Why do you need an array to do this?

You can get your answer in an Excel formula by using

=LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

Or VBA

Sub CountDots()
    Dim str As String
    str = "A01.10.10.10"
    MsgBox Len(str) - Len(Replace(str, ".", vbNullString))
End Sub

No need for an array. If you are going to do something else with the string and need it in an array though then use @JNevill answer

Upvotes: 2

JNevill
JNevill

Reputation: 50034

You can use Split() for this:

myArray = Split(Range("A1"), ".")

That will "Split" Range("A1") contents by a period into an array.

You could also just go straight to the return of the split() function to get the ubound since the return is an array (skipping the need to dim the array):

UBound(Split(Range("A1"),"."))

Which would spit out 3 given your input.

Upvotes: 4

Cyril
Cyril

Reputation: 6829

You can found the "." in a single cell using a formula:

=Len(targetcell)-Len(Substitute(targetcell,".",""))

Counts the number of cells, then counts the number of cells after the "." has been replaced with "" (nothing).

You wouldn't need an array for that.

Upvotes: 1

Related Questions