extracting strings from cell
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. |
extracting strings from cell
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. |
extracting strings from cell
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. |
extracting strings from cell
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 |
extracting strings from cell
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. |
extracting strings from cell
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 |
extracting strings from cell
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 |
extracting strings from cell
============
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 |
All times are GMT +1. The time now is 07:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com