Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Substitute letters in a string (Excel xp)

    I have a workbook attached that on the "before" sheet has letters in col C, F, I etc. These letters have to be substituted for the one on the "code" sheet. If you look at the "after" sheet you can see i've done one (in red). They need to be separated by commas. I have tons of these to do, the letter will always appear in COL C, F, I etc. but i don't know how many columns there will be. I want to make a macro to go through the sheet and make the changes but don't know how to get started. Thank you for the help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Substitute letters in a string (Excel xp)

    1) I would create a FUNCTION which reads gets the "text string" from the cell. loops thru each letter and reads from the "code table" the replacement and then adds the replacement and the comma.

    2) Then write a subroutine that goes through all the selected cells, goes through every 3rd column, goes down the row and for each item replaces the "value" with the result of the function call from the value.

    activesheet.usedrange.specialcells(xlcelltypeconst ants,xltextvalues).select
    'Will select ONLY the extvalues in the used range, you can loop through the cells in this range and

    if cell.Column mod 3 = 0 then cell.value = MyFunction(cell.value)

    where myfunction is the function in item 1

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Substitute letters in a string (Excel xp)

    Here's an oddball approach. Please test:

    Sub jha900Subst()
    Dim rngTarg As Range, rngCell As Range
    Dim intC As Integer
    On Error Resume Next
    Set rngTarg = Intersect(ActiveSheet.UsedRange.EntireRow, Columns("C:IV"), _
    Rows("3:65536")).SpecialCells(xlCellTypeConstants, 2)
    If rngTarg Is Nothing Then Exit Sub
    ' Tokenize with a number in text form (because the code letters overlap)
    For intC = 1 To 12
    rngTarg.Replace _
    What:=Worksheets("Code").Cells(intC, 2).Value, _
    Replacement:=intC & ",", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Next intC
    ' Replace token numbers with actual code (backwards to handle multidigit numbers)
    For intC = 12 To 1 Step -1
    rngTarg.Replace _
    What:=intC & ",", Replacement:=Worksheets("Code").Cells(intC, 3).Value & ",", _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Next intC
    ' Strip last comma
    For Each rngCell In rngTarg
    rngCell.Value = Left(rngCell.Value, Len(rngCell.Value) - 1)
    Next rngCell
    Set rngTarg = Nothing
    End Sub

    You should think about fixing this messed up data at the source rather than writing all these restructuring macros provided in the last few threads.. As many have noted, putting this kind of data into a flat form database is much easier in terms of producing Pivot Tables and other data analysis work. And if you need to do this much coding, time to get into an Excel VBA book. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Substitute letters in a string (Excel xp)

    AN interesting solution.

    Your approach is different than mine in this question.

    I suspect yours is also faster, excel doing find /replace over my coding in a function to look at each letter and then "lookup".

    I was shying away from find/replace since I was worried about "replacing" the overlaps. Your initial coding is a technique I will have to keep in mind in similar situations

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Substitute letters in a string (Excel xp)

    "Interesting"? I think you mean "goofy". <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I should make the part that picks up the from-and-to codes more flexible, but compensated work awaits me..
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Substitute letters in a string (Excel xp)

    Hi jha,

    How about a non-macro solution (ie formula)? Check out the attached and, in particular, Column D on the 'Before' sheet.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Substitute letters in a string (Excel xp)

    oh my goodness. That is quite a formula. How does it insert the commas or can you give me a basic <img src=/S/baby.gif border=0 alt=baby width=15 height=15> lesson on how it works. Thank you so much.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Substitute letters in a string (Excel xp)

    Hi jha,

    The formula uses a series of IF tests to check the length of the source string and, if there are still characters to process, uses REPLACE to change the specified character with a value in your table on the 'Code' sheet, followed by a space. After processing all strings, the formula uses TRIM to get rid of any unwanted spaces, then SUBSTITUTE to change all the remaining spaces to commas.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Substitute letters in a string (Excel xp)

    This approach worked very well. Thank you very much. Can you explain the set statements because i have several of these tables one underneath each other on the same sheet. The macro seaches the heads and replaces them also and i need to bypass them, for instance ABC Inc., Acme Company etc. The macro is taking these heading names and replacing them with the "codes". <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> believe it or not i have read vba books but would never have thought of this approach. thank u for the help again.

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Substitute letters in a string (Excel xp)

    I assume you mean

    Intersect(ActiveSheet.UsedRange.EntireRow, Columns("C:IV"), _
    Rows("3:65536")).SpecialCells(xlCellTypeConstants, 2)

    Intersect method is just like geometric intersect; it sets the range where -all- the specified argument ranges overlap. There are better ways of doing this, I used Intersect in that particular case because it narrows the range down easily. If you have a series of areas on one sheet, range name each one of them, -excluding- the heading, and then cycle through the ranges like this:

    Sub rangecycle()
    Dim varRngIn As Variant
    Dim rngTarget As Range
    For Each varRngIn In Array("Area1", "Area2", "Area3") ' Area1, etc, are the named ranges
    Set rngTarget = Range(varRngIn).Cells ' replaces above Intersect() thunk
    'run process
    Set rngTarget = Nothing
    Next varRngIn
    End Sub

    Alternatively you could range-name the headings using "Header" or some such key word, and process each row in the WS via loop, after first checking and skipping rows whose range-names include the key word. You are going to a lot of work because of the data structure you have to deal with.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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