Consulting

Results 1 to 10 of 10

Thread: Manipulate a string

  1. #1

    Unhappy Manipulate a string

    In range a1 to a500 (lastrow is variable , it could be A300 one day and A700 the next) I have text that looks similar to this

    "aaaa, aaaa, aaaa, aaedwerr, dfsfdgf, afdgfdg, adgfdgf, adffgffg,"

    I need to remove the duplicates so I would be left
    with

    "aaaa, aaedwerr, dfsfdgf, afdgfdg, adgfdgf, adffgffg,"

    the split this out across the cells to the left

    so if this was in a1 "aaaa, aaaa, aaaa, aaedwerr, dfsfdgf, afdgfdg, adgfdgf, adffgffg,"

    then
    b1 = aaaa
    c1 = aaedwerr
    d1 = afdgfdg
    e1 = adgfdgf
    f1 = adffgffg

    can anyone help ?

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [VBA]
    Option Explicit
    Sub SplitStuff()
    Dim txt, s
    Dim i As Long, Col As Long
    For Each s In Selection
    Col = 1
    txt = Split(s, ",")
    For i = 0 To UBound(txt)
    If Application.CountIf(Range(s.Offset(, 1), s.Offset(, Col + 1)), Trim(txt(i))) = 0 Then
    s.Offset(, Col) = Trim(txt(i))
    Col = Col + 1
    End If
    Next
    Next
    End Sub

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3

    Red face Thanks

    How can I remove duplicates in the string?

  4. #4
    VBAX Tutor nst1107's Avatar
    Joined
    Nov 2008
    Location
    Monticello
    Posts
    245
    Location
    Unless you need this done programmatically... Here is a good article showing how to delete duplicates in Excel 2003 relatively painlessly. http://office.microsoft.com/en-us/ex...CL100570551033 Excel 2007 has an even easier one-click button to do it for you. Either way, after that you can use the TRANSPOSE function to split the list out to the right.

  5. #5
    no it needs to be done by programming

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Create a new string in the loop and write it in at the end
    [VBA]
    str = str & Trim(txt(i)) & ", "
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    confused, im not that good with the programming.. sorry mdmackillop

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [vba]Option Explicit
    Sub SplitStuff()
    Dim txt, s As Range, str As String
    Dim i As Long, Col As Long
    For Each s In Selection
    Col = 1
    txt = Split(s, ",")
    For i = 0 To UBound(txt)
    If Application.CountIf(Range(s.Offset(, 1), s.Offset(, Col + 1)), Trim(txt(i))) = 0 Then
    s.Offset(, Col) = Trim(txt(i))
    str = str & Trim(txt(i)) & ", "
    Col = Col + 1
    End If
    Next
    s.Value = Left(str, Len(str) - 2)
    Next
    End Sub
    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9

    This doesnt seem to work, i have attached dummy data

    This dummy data shows from a3 downwards errors ,

    Each cell could have 1 error or more. The errors are split by a ",".
    Some cells can be blank some , may have "n/a" or "No_Error".

    I need to normalise the data then split it along the cells to the right OF each row.

    normalising data:
    N/a or No_error are standalone, they cannot have other entries next to them eg

    ZA26, No_Error

    this has been input incorrectly by the inputter. This should be ZA26 and not ZA26, No Error.

    same rule applies for N/A.

    Can u help ?

    Can

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    This doesnt seem to work, i have attached dummy data
    Does it not work on the data sample you provided, or on your new sample data?
    If the former, where does it break down? If the latter, you seem to asking a totally different question.

    I'm not clear what should be ignored/output. Rather than 10,000 rows of meaningless data, how about a dozen rows showing possible combinations and desired outputs, with explanatory notes.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •