Solved

Excel, split cell

Posted on 2000-04-18
26
970 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
need copy macro to select range to copy 23 48
Excel to show a dynamic Picklist at level2 2 22
Excel VBA Script 9 53
Problem to macro 5 18
Outlook Free & Paid Tools
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

737 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