Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    conditional text replacement (2003)

    hi all,
    I have a column that has text data, the text data sample is xxxc-xxxe-xxxg-xxxj, the c&e represents one group name
    TIA
    dubdub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: conditional text replacement (2003)

    Could you post a sample workbook with some concrete examples of the 'input' and the desired 'output'?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: conditional text replacement (2003)

    sample attached.

    Regards,
    TIA
    dubdub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: conditional text replacement (2003)

    It could probably be done using formulas only, but it'd get rather complicated. In the attached workbook, I wrote a user-defined function ExtractNames:
    <code>
    Function ExtractNames(ByVal CellVal As String) As String
    CellVal = CellVal & "-"
    If InStr(CellVal, "C-") > 0 Or _
    InStr(CellVal, "E-") > 0 Then
    ExtractNames = " & UTRA"
    End If
    If InStr(CellVal, "G-") > 0 Or _
    InStr(CellVal, "J-") > 0 Or _
    InStr(CellVal, "M-") > 0 Then
    ExtractNames = ExtractNames & " & MANS"
    End If
    If InStr(CellVal, "P-") > 0 Or _
    InStr(CellVal, "U-") > 0 Or _
    InStr(CellVal, "S-") > 0 Then
    ExtractNames = ExtractNames & " & LSTR"
    End If
    If Not ExtractNames = "" Then
    ExtractNames = Mid(ExtractNames, 4)
    End If
    ExtractNames = ExtractNames
    End Function
    </code>
    To get the GName for C3, use the formula
    <code>
    =ExtractNames(C3)
    </code>
    You can fill this formula down as far as needed.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: conditional text replacement (2003)

    Many thanks HansV.

    dubdub
    TIA
    dubdub

Posting Permissions

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