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.

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


"Anand" wrote in message
...
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.

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
'<<================


---
Regards.
Norman


"Anand" wrote in message
...
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.

Hi Arnand,

I should have added that the string

"AAA BBB CCC DDD"

is in fact double spaced.

If this is intentional, change:

arr = Split(sStr, Space(1))

to

arr = Split(sStr, Space(2))




---
Regards.
Norman

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.


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Anand" wrote:

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.

Or if the number of spaces were unknown:

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



Norman Jones wrote:

Hi Arnand,

I should have added that the string

"AAA BBB CCC DDD"

is in fact double spaced.

If this is intentional, change:

arr = Split(sStr, Space(1))

to

arr = Split(sStr, Space(2))

---
Regards.
Norman

--

Dave Peterson

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!


---
Regards.
Norman

============
Or if the number of spaces were unknown:

arr = Split(sStr, Space(1))
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