Solved

Excel, split cell

Posted on 2000-04-18
26
964 Views
Last Modified: 2009-07-29
I have a cell (D12) that contains the string "ABCD EFGH IG LMD"

I need to take the contents of the cell and split it seperate keywords, in a variable.

ie. keyword(1)="ABCD", keyword(2)="EFGH" etc...

How can I do this ?

Lee
0
Comment
Question by:lee_jd
  • 5
  • 5
  • 5
  • +5
26 Comments
 
LVL 3

Expert Comment

by:DFI
ID: 2726055
1 Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one column wide.

2 On the Data menu, click Text to Columns.
3 Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

d:o)
0
 
LVL 3

Expert Comment

by:DFI
ID: 2726061
oops... you asked "in a variable"
Sorry
0
 
LVL 22

Expert Comment

by:ture
ID: 2726064
Lee,

If you are using Excel 2000, you can use the Split function. It splits the string into a one-dimensional array.

Please note that the first element of the array is keyword(0), not keyword(1). I hope that is not a problem.

Sub SplitCellValue()
  Dim keyword As Variant
  keyword = Split(Range("D12").Value, " ")
  MsgBox keyword(0)
  MsgBox keyword(1)
  MsgBox keyword(2)
  MsgBox keyword(3)
End Sub

Ture Magnusson
Karlstad, Sweden
0
 
LVL 6

Expert Comment

by:Marine
ID: 2726305
Ture this will be needing constant modification wouldn't it ? Unless he always has a fixed number of separated strings in there. Other wise he would have to loop to find the " " between them and then use the split.
0
 
LVL 22

Expert Comment

by:ture
ID: 2726342
Marine,

Well, the split itself will work for any-length-strings.

The MsgBox is just an example to show what has happened. To make it more flexible, use this:

Sub SplitCellValue()
  Dim keyword As Variant
  Dim x As Integer

  keyword = Split(Range("D12").Value, " ")
  For x = 0 To UBound(keyword)
    MsgBox keyword(x)
  Next x
End Sub

/Ture
0
 
LVL 6

Expert Comment

by:Marine
ID: 2726378
Yes i understand how it works. Only this time its better written.
0
 
LVL 2

Author Comment

by:lee_jd
ID: 2726518
I am afraid I only use Excel97.

Is there another way ?

0
 
LVL 6

Expert Comment

by:Marine
ID: 2726539
another way would be using Instr() with Mid() . While looping though the cell value. Trying to find the occurance of " ".
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2726550
I'm sure that I have a function at home that does this. I don't use it anymore but, if I recall correctly, you can actually specify what delimiter you want to use as well. I'll dig it out tonight if I can find it.
0
 
LVL 3

Expert Comment

by:DFI
ID: 2726716
It seems to work. Sure it could be done better but... d:o)

Sub Split97()
Dim keyword() As String
WordToSplit = Range("D12").Value
Wordlength = Len(WordToSplit)
If Wordlength = 0 Then Exit Sub

SubWord = 1
StartSubWord = 1
EndSubWord = InStr(1, WordToSplit, " ")


While EndSubWord > StartSubWord
  ReDim Preserve keyword(SubWord)
  keyword(SubWord) = Mid(WordToSplit, StartSubWord, _
                       EndSubWord - StartSubWord)

'MsgBox keyword(SubWord)

  SubWord = SubWord + 1
  StartSubWord = EndSubWord + 1
  EndSubWord = InStr(StartSubWord, WordToSplit, " ")

Wend

ReDim Preserve keyword(SubWord)
keyword(SubWord) = Mid(WordToSplit, StartSubWord, _
                       Wordlength - StartSubWord + 1)

End Sub
0
 
LVL 22

Expert Comment

by:ture
ID: 2726838
Lee,

Here is my contribution for Excel 97 VBA:

Sub SplitString()
  Dim t As String
  Dim p As Integer
  Dim x As Integer
  Dim keyword() As String
 
  t = Trim(Range("D12").Value)
  Do While InStr(1, t, " ") <> 0
    x = x + 1
    ReDim Preserve keyword(1 To x)
    p = InStr(1, t, " ")
    keyword(x) = Trim(Left(t, p))
    t = Trim(Mid(t, p))
  Loop
End Sub

/Ture
0
 
LVL 3

Expert Comment

by:DFI
ID: 2726921
Ture,
I tested your solution.
It doesn't seem to take the last group
i.e.
* if the D12-cell doesn't contain any space character, the keyword array remains empty
* if the cell contains "ABCD EFGH IG LMD", you get "ABCD", "EFGH" and "IG", but not "LMD"!
0
 
LVL 6

Expert Comment

by:Marine
ID: 2726944
yes but if you test his code with delimter "," it will work. It all depends on the delimiter that is being used.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:ture
ID: 2726970
DFI,

Thanks for pointing that out. This should be better...

Sub SplitString()
  Dim t As String
  Dim p As Integer
  Dim x As Integer
  Dim keyword() As String
   
  t = Trim(Range("D12").Value) & " "
  Do While InStr(1, t, " ") <> 0
    x = x + 1
    ReDim Preserve keyword(1 To x)
    p = InStr(1, t, " ")
    keyword(x) = Trim(Left(t, p))
    t = LTrim(Mid(t, p))
  Loop
End Sub

/Ture
0
 
LVL 3

Expert Comment

by:DFI
ID: 2726991
Yeh... With a little more enhancement, it'll look like mine... with shorter variable names
d:o)
0
 
LVL 22

Expert Comment

by:ture
ID: 2727128
Hi All,

I have now lengthened the variable names. :o)
And made it into a little function.
It doesn't look TOO much like your's DFI...

Function SplitText(ByVal TextToSplit As String) As Variant
  Dim SplitCount As Integer
  Dim SpacePos As Integer
  Dim SplitArray() As Variant
 
  TextToSplit = Trim(TextToSplit) & " "
  Do While InStr(1, TextToSplit, " ") <> 0
    SplitCount = SplitCount + 1
    ReDim Preserve SplitArray(1 To SplitCount)
    SpacePos = InStr(1, TextToSplit, " ")
    SplitArray(SplitCount) = Trim(Left(TextToSplit, SpacePos))
    TextToSplit = LTrim(Mid(TextToSplit, SpacePos))
  Loop
  SplitText = SplitArray
End Function

Sub LetUsSeeIfItWorks()
  Dim KeyWords As Variant
  KeyWords = SplitText(Range("D12").Value)
  Debug.Print KeyWords(4)
End Sub

/Ture
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2727897
Ok, lee_jd, I've found my old function. You will see that you can specify what delimiter you want. And you're not restricted to a 1-character delimiter either: you could have a delimiter of "delim" and it will split your text according to where it finds "delim" in your string.

'
' Splits 'TextString' into sections as governed by the Delimiter and returns in the array 'Sections()'.
' Macro recorded 06/01/96 by Robin Davis
'
Sub SplitIntoSections(ByVal TextString As String, ByVal Delimiter As String, _
    ByRef Sections() As String, ByRef NoOfSections As Integer)

    Dim StartAtChar As Integer, EndAtChar As Integer
   
    On Error GoTo ErrorDetectedInSplitIntoWords

    'Need to ensure that the TextString to be split has the delimiter at the beginning of the string -

    'If it isn't, then one will need to be added
    StartAtChar = Len(Delimiter)
    Do Until Mid(Delimiter, StartAtChar) <> Left(Trim(TextString), Len(Delimiter) - StartAtChar + 1)
        StartAtChar = StartAtChar - 1
        If StartAtChar = 0 Then Exit Do
    Loop
    TextString = Left(Delimiter, StartAtChar) & Trim(TextString)
    If Len(TextString) = Len(Delimiter) Then NoOfSections = 1: Exit Sub

    'Need to ensure that the TextString to be split has the delimiter at the end of the string -
    'If it isn't, then one will need to be added
    StartAtChar = 1
    Do Until Left(Delimiter, StartAtChar) <> Right(TextString, StartAtChar)
        StartAtChar = StartAtChar + 1
    Loop
    TextString = TextString & Mid(Delimiter, StartAtChar)

    'Start splitting the text
    ReDim Sections(1 To 1)
    StartAtChar = InStr(1, TextString, Delimiter): NoOfSections = LBound(Sections) - 1
    Do Until InStr(StartAtChar + 1, TextString, Delimiter) = 0
        If StartAtChar + Len(Delimiter) >= Len(TextString) Then
            'Reached the end of the TextString
            Exit Do
        Else
            EndAtChar = InStr(StartAtChar + 1, TextString, Delimiter)
        End If
        If EndAtChar - StartAtChar > Len(Delimiter) Then
            NoOfSections = NoOfSections + 1
            ReDim Preserve Sections(1 To (UBound(Sections) + 1))
            Sections(NoOfSections) = Mid(TextString, StartAtChar + Len(Delimiter), EndAtChar - StartAtChar - Len(Delimiter))
        End If
        StartAtChar = EndAtChar
        ReDim Preserve Sections(1 To NoOfSections)
    Loop
Exit Sub
ErrorDetectedInSplitIntoWords:
    Stop
    Resume
End Sub

You will see that you need to pass a string array (dimensioned or not, it doesn't care), and it will fill it for you and return it.

As I said, it's an old routine of mine and it could probably do with a revamp. However, let me know what you think or how you would like it modified, and I'll get to it....that is if you haven't already plumped for one of the others' solutions :-) .
0
 
LVL 2

Author Comment

by:lee_jd
ID: 2734427
I'm sorted now, thanks for all your help.

This brings me on to the next problem.

When I have :

keyword(0)="aa"
keyword(1)="bb"
keyword(2)="cc"

I have a worksheet with :

Name        Subject
Mark        AA BB
John        CC AA BB
Joe         BB

Ok, I need to search on this list with the keywords(x) array.  I can do it with the OR condition simply by searching, however, I'm a little stuck with the AND condition.

i.e. if the user enters keywords CC AA then I want it to return the John row.

When a row is found it is copied to a results worksheet.

Lee
0
 
LVL 13

Expert Comment

by:cri
ID: 2851509
lee_jd, please settle the question. If you still need an answer to your follow up question, ask a new one. Nobody is working on this question anymore, but it would be a pity to lose the content to autodeletion. Hope you understand
0
 
LVL 3

Expert Comment

by:darinw
ID: 2909600
Hello everyone,

lee_jd - One question = one solution. It is not fair to the Experts to hold the points hostage while you ask more questions under the original post.

I believe you have gotten an A grade amount of help so you need to accept one of these fine comments the Experts have made as an answer and post a new question for your new problem.

darinw
Customer Service
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 3080931
Still no Lee_jd,

May the best post as an answer...

Calacuccia
0
 
LVL 4

Accepted Solution

by:
Noggy earned 200 total points
ID: 3083874
Hmm, I think mine is the best  :-) - so I'll post as answer. If either of you other experts dispute it, let me (and especially lee_jd) know.

PROPOSED SOLUTION: Please see my SplitIntoSections() function in the comment Date: Tuesday, April 18 2000 - 07:25PM BST
0
 
LVL 2

Author Comment

by:lee_jd
ID: 3110234
Answer accepted
0
 
LVL 4

Expert Comment

by:Noggy
ID: 3113990
Cheers, lee_jd. I'd just like to say though that a little consideration may be in order in providing feedback to comments. After all, 3 months is a little long....
0
 
LVL 2

Author Comment

by:lee_jd
ID: 3125931
Yep.  Soz for that.  I've been away in Russia where I had no web access.

The solution presented worked fine.  Not only did it solve my problem, it also educated me in other aspects of VBA for Excel.

Many thanks,

Lee
0
 
LVL 4

Expert Comment

by:Noggy
ID: 3135859
Apology accepted. Russia?! That's rather extreme, isn't it? How did you manage to get parcelled off there - and without Web access too :-) ? I always demand that I have Web access wherever I go - but my company doesn't send me anywhere so I don't get to exercise that demand too often :-).

Re education: good stuff. It's nice to know that you're helping someone along the knowledge path. Any aspect in particular?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now