Spliting one string into multiple and inserting into multiple colunms

[muralikrishna1983]

I want to know the macro or query to split the string in one single colunm and insert those into multiple colunms(that may be a sentence)
like for example

colunm1 contains:

1234 daily !@#$(these words are separated by space)

these things must be splited in to
1234,daily,!@#$ and inserted into three different colunms
colunm2 colunm3 colunm4
1234 daily !@#$

these colunms are in same table.

[Minion]

One way to do this is to use a parsing function like the one below. This function is passed a string and a list of delimeters (such as " " for space). It will then go through the string and split the string into an array. Once you have the array use a for...next loop to populate the remaining fields.

Here is the code of the parsing function. It shouldn't need much if any editing to get working:

Public Function parse(ByVal inString, Optional ByVal delimiters)
'Take a string, and return it as a one dimensional array
' of individual values as delimited by any of several
' characters. None of those characters are returned in
' the result. Provide a default list of delimiters, which
' should come from registry. But allow override.
 
    Dim delimitList, oneChar, aWord, codeCount
    Dim arrayCodes()
 
    If IsMissing(delimiters) Then
        'We should get these from Registry
        delimitList = " ,/!|"
'Characters recognized as delimiters
 
    Else
        delimitList = delimiters
'user can override if needed
 
    End If
    Dim i, j, k
    i = Len(inString)
    For j = 1 To i
'Read one character at a time
 
        oneChar = VBA.Strings.Mid(inString, j, 1)
        k = InStr(delimitList, oneChar)
'Is this one a delimiter?
        If k = 0 Then
            aWord = aWord & oneChar
'If is isn't, add to the current word
        End If
        If k <> 0 Or j = i Then
'If it is, or if we're finished
            If aWord > "" Then
                codeCount = codeCount + 1
                ReDim Preserve arrayCodes(codeCount)
                arrayCodes(codeCount) = aWord
'Save new word
                aWord = ""
            End If
        End If
    Next j
    parse = arrayCodes
'Return the array
End Function
 

Hope this helps

- Minion -

[muralikrishna1983]

hi minion
thankyou for your response
But i am not understanding this please give a detailed explination
on this ,actually i do not know anything about macro please help me

[missinglinq]

You really need to provide a little more information. Is the original string always going to be three words long? Are the words always going to be separated by a comma?

Welcome, both of you, to TheScripts!

Linq ;0)>

[ADezii]

I want to know the macro or query to split the string in one single colunm and insert those into multiple colunms(that may be a sentence)
like for example

colunm1 contains:

1234 daily !@#$(these words are separated by space)

these things must be splited in to
1234,daily,!@#$ and inserted into three different colunms
colunm2 colunm3 colunm4
1234 daily !@#$

these colunms are in same table.

Here is compact code that will work as long as you have only 3 values in [Column1] separated by spaces. It assumes your Table Name is tblTest, and your Field Names are [Column1], [Column2], [Column3], and [Column4] where [Column1] contains the multiple values. Any questions, feel free to ask.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim varSplit As Variant, intCounter As Integer
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
 
If MyRS.RecordCount > 0 Then
  Do While Not MyRS.EOF
  varSplit = Split(MyRS![Column1], " ")
    MyRS.Edit
      For intCounter = 0 To UBound(varSplit)    'will be 2
        MyRS![Column2] = varSplit(0)
        MyRS![Column3] = varSplit(1)
        MyRS![Column4] = varSplit(2)
      Next
    MyRS.Update
    MyRS.MoveNext
  Loop
End If
 
MyRS.Close
Set MyRS = Nothing

[muralikrishna1983]

You really need to provide a little more information. Is the original string always going to be three words long? Are the words always going to be separated by a comma?

Welcome, both of you, to TheScripts!

Linq ;0)>

hi,
the string length will be differing,And the string is not separated by comma,It is separated by SPACE
Thank You

[muralikrishna1983]

Here is compact code that will work as long as you have only 3 values in [Column1] separated by spaces. It assumes your Table Name is tblTest, and your Field Names are [Column1], [Column2], [Column3], and [Column4] where [Column1] contains the multiple values. Any questions, feel free to ask.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim varSplit As Variant, intCounter As Integer
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
 
If MyRS.RecordCount > 0 Then
  Do While Not MyRS.EOF
  varSplit = Split(MyRS![Column1], " ")
    MyRS.Edit
      For intCounter = 0 To UBound(varSplit)    'will be 2
        MyRS![Column2] = varSplit(0)
        MyRS![Column3] = varSplit(1)
        MyRS![Column4] = varSplit(2)
      Next
    MyRS.Update
    MyRS.MoveNext
  Loop
End If
 
MyRS.Close
Set MyRS = Nothing

Hi
thankyou for your reply
In the Colunm1 string is not constant and It varies alway
thankyou

[muralikrishna1983]

Here is compact code that will work as long as you have only 3 values in [Column1] separated by spaces. It assumes your Table Name is tblTest, and your Field Names are [Column1], [Column2], [Column3], and [Column4] where [Column1] contains the multiple values. Any questions, feel free to ask.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim varSplit As Variant, intCounter As Integer
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
 
If MyRS.RecordCount > 0 Then
  Do While Not MyRS.EOF
  varSplit = Split(MyRS![Column1], " ")
    MyRS.Edit
      For intCounter = 0 To UBound(varSplit)    'will be 2
        MyRS![Column2] = varSplit(0)
        MyRS![Column3] = varSplit(1)
        MyRS![Column4] = varSplit(2)
      Next
    MyRS.Update
    MyRS.MoveNext
  Loop
End If
 
MyRS.Close
Set MyRS = Nothing

Hi,
Can you give this same thing to use in excel
And the other thing is my string in the column1 will be varying
it may be
case1:hi john hw r u
case2:wt r u doning
case3 can we meet today or not
case4:when can we meet so
like the above the strings in my column1 will be changing,
if you can give me solution for this please give me in access and excel. OR
please give the previous solution to use in EXCEL
thankyou

[ADezii]

Hi,
Can you give this same thing to use in excel
And the other thing is my string in the column1 will be varying
it may be
case1:hi john hw r u
case2:wt r u doning
case3 can we meet today or not
case4:when can we meet so
like the above the strings in my column1 will be changing,
if you can give me solution for this please give me in access and excel. OR
please give the previous solution to use in EXCEL
thankyou

1. I'm asuming that your Fields are already defined in your Table namely: Column1, Column2, Column3, Column4, Column5, etc. Is this the case, and if so how many pre-defined Columns exist in the Table?
   
2. You previously stated that the values in Column1 could also be delimited by a Comma in addition to a space, does this still hold?
   
3. Would the Delimiter be consistent for all the Records in a Table, or could you possibly have the scenario listed below?
   

   Record 1    Value1, Value2, Value3, etc.       'Comma Delimiter
   Record 2    Value1 Value2 Value3, etc          'Space Delimiter

[missinglinq]

Two things:

First, we've all been concentrating on parsing your string and noone's actually asked: Exactly what are you trying to do here? As ADezii asked, how many columns are you going to have to cover ever possibilty in Column1?

Secondly, you have cost a number of experts here a great deal of time! You initially stated your problem, showing an example string with three parts, separated by commas. When asked if this is the format the string will always be in, you responded a day and a half later, saying that no, the string will vary in length and no, despite your example being separated by commas, they will actually be separated by spaces! Then you come back again, demanding an answer to the problem using Excel!

Please take the time to read the Posting Guidelines paying special attention to the section titled “Give as much detail as possible!”

Linq ;0)>

[ADezii]

Two things:

First, we've all been concentrating on parsing your string and noone's actually asked: Exactly what are you trying to do here? As ADezii asked, how many columns are you going to have to cover ever possibilty in Column1?

Secondly, you have cost a number of experts here a great deal of time! You initially stated your problem, showing an example string with three parts, separated by commas. When asked if this is the format the string will always be in, you responded a day and a half later, saying that no, the string will vary in length and no, despite your example being separated by commas, they will actually be separated by spaces! Then you come back again, demanding an answer to the problem using Excel!

Please take the time to read the Posting Guidelines paying special attention to the section titled “Give as much detail as possible!”

Linq ;0)>

Nicely said, missinglinq.

[muralikrishna1983]

Two things:

First, we've all been concentrating on parsing your string and noone's actually asked: Exactly what are you trying to do here? As ADezii asked, how many columns are you going to have to cover ever possibilty in Column1?

Secondly, you have cost a number of experts here a great deal of time! You initially stated your problem, showing an example string with three parts, separated by commas. When asked if this is the format the string will always be in, you responded a day and a half later, saying that no, the string will vary in length and no, despite your example being separated by commas, they will actually be separated by spaces! Then you come back again, demanding an answer to the problem using Excel!

Please take the time to read the Posting Guidelines paying special attention to the section titled “Give as much detail as possible!”

Linq ;0)>

Hi,
Actually,
1.I will get a string into my first column1
2.I don't no its length
3.I am having n-number of columns in my table or sheet
4.My delimiter is "space"
5.My database may be access or excel
So please suggest me the solution.
thankyou

[Minion]

Hi,
Actually,
1.I will get a string into my first column1
2.I don't no its length
3.I am having n-number of columns in my table or sheet
4.My delimiter is "space"
5.My database may be access or excel
So please suggest me the solution.
thankyou

Thank you for clearifing what it is your looking for somewhat. However, the medium for your database will greatly determine what type of solution is produced. If we were to give you a suggestion for how to solve your problem in access it would differ from one given for access. You also mentioned that you have limited to no knowledge of VBA or Macros so any solution you are given will be a challenge for you to implement. These are just a couple of the challenges still facing your situation; however I'll keep working on a solution that you should be able to utilize as more information is made available. Namely we need to know exactly what you have, what you will need, and what program you will need it for as the code will differ quite a bit.

Keep us posted.

- Minion -