extracting strings from cell

[Anand]

I have multiple names stored in a cell. I want to extract individual
names out of the cell. Appreciate any help in this regard.

The cell contains "AAA BBB CCC DDD"
I want to extract this into an array:
Names(0) = "AAA"
Names(1) = "BBB"
Names(2) = "CCC"
Names(3) = "DDD"

Thanks,
Anand.

 

[Rick Rothstein \(MVP - VB\)]

I am assuming that when you say the cell contains
"AAA BBB CCC DDD", you mean without the surrounding quote marks. Give
this code snippet a try...

Dim Names() As String
With Worksheets("Sheet1")
Names = Split(.Range("A1").Value, " ")
End With

' Proof that it worked
Dim X As Long
For X = 0 To UBound(Names)
Debug.Print Names(X)
Next

Change the Sheet1 and A1 references to match your needs.

Rick

 

[Norman Jones]

Hi Arnand,

Try:

'================>>
Public Sub Tester2()
Dim arr As Variant
Dim i As Long
Const sStr As String = "AAA BBB CCC DDD"

arr = Split(sStr, Space(1))

For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i

End Sub
'<<================

 

[Norman Jones]

Hi Arnand,

I should have added that the string

"AAA BBB CCC DDD"

is in fact double spaced.

If this is intentional, change:

 

[John Bundy]

If it always has a space, look into the split function, it splits data at a
delimiter into an array, if you don't like that then just step through each
character in a for next loop
*pseudocode*

For i=1 to len(cells(1,1))
myString=myString & trim(mid(cells(1,1),i,1))

if mid(cells(1,1),i,1)="" then
Names(arrayIndex)=mystring
mystring=Nothing
arrayIndex=arrayIndex+1
end if

next

that looks like it should do what you ask.

 

[Dave Peterson]

Or if the number of spaces were unknown:

arr = Split(sStr, Space(1))
becomes:
arr = Split(application.trim(sStr), Space(1))

 

[Norman Jones]

Hi Dave,

============

Or if the number of spaces were unknown:

arr = Split(sStr, Space(1))

becomes:
arr = Split(application.trim(sStr), Space(1))
============


Excellent improvemnt!

Thankyou!

 

[Rick Rothstein \(MVP - VB\)]

============

becomes:
arr = Split(application.trim(sStr), Space(1))
============

Excellent improvemnt!

Although some may not consider this an improvement because it is not as
clear as to what is going on, but the space character is the default
delimiter for the Split function, so, in this case, it can be omitted...

arr = Split(application.trim(sStr))

Rick