extracting strings from cell

  • Thread starter Thread starter Anand
  • Start date Start date
A

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.
 
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
 
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
'<<================
 
Hi Arnand,

I should have added that the string

"AAA BBB CCC DDD"

is in fact double spaced.

If this is intentional, change:
 
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.
 
Or if the number of spaces were unknown:

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

Excellent improvemnt!
Click to expand...

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
 
Back
Top