Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is a weird request. I've got a user who needs to be able to search a column of dollar figures, and replace the number in the 100ths position with a character / letter, depending on what the number is. E.g., 0=}, 1=A, 2=B, etc. Is there a formula that could do this. I thought about an IF statement, but there is no "Else", just "If 0, then }, If 1, then A, etc." AND I wasn't sure how to specify that it only look at the 100ths number (not the whole number). Same for Find / Replace - not sure to specify that it look at a specific digit.

    Any help would be greatly appreciated.

    Satiria

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use VBA code to do this, but what is the idea behind it? Changing digits to characters will make the values invalid - you won't be able to perform any calculations with them any more.
    And what is the logic behind 0 > } and 1 > A ? } and A aren't "neighbor" characters.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans! Wassup!

    They aren't doing calculations on the numbers. It's for export / import purposes. They have an exported list from one program and they are trying to import into another program which, for some strange reason, wants a character in that last column, rather than a number. And I have no idea the logic behind which number = which character. They just gave me a list.

    I'm not that familiar / good with VBA code. Would this be something easy to create? Guess you could make a macro that they could run as necessary on a spreadsheet to convert those digits?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The following is rather clunky, it could be made more elegant.
    Select the cells to be modified, then run the macro

    [codebox]Sub RuinNumbers()
    Dim oCell As Range
    Dim strVal As String
    Dim p As Integer
    Dim c As String
    For Each oCell In Selection.Cells
    strVal = oCell.Text
    p = InStrRev(strVal, ".")
    If p = 0 Then
    If Len(strVal) > 2 Then
    c = Mid(strVal, Len(strVal) - 2, 1)
    Select Case c
    Case "0"
    c = "}"
    Case "1"
    c = "A"
    Case "2"
    c = "B"
    Case "3"
    c = "C"
    Case "4"
    c = "D"
    Case "5"
    c = "E"
    Case "6"
    c = "F"
    Case "7"
    c = "G"
    Case "8"
    c = "H"
    Case "9"
    c = "I"
    End Select
    Mid(strVal, Len(strVal) - 2, 1) = c
    oCell.Value = strVal
    End If
    ElseIf p > 3 Then
    c = Mid(strVal, p - 3, 1)
    Select Case c
    Case "0"
    c = "}"
    Case "1"
    c = "A"
    Case "2"
    c = "B"
    Case "3"
    c = "C"
    Case "4"
    c = "D"
    Case "5"
    c = "E"
    Case "6"
    c = "F"
    Case "7"
    c = "G"
    Case "8"
    c = "H"
    Case "9"
    c = "I"
    End Select
    Mid(strVal, p - 3, 1) = c
    oCell.Value = strVal
    End If
    Next oCell
    End Sub[/codebox]

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Had to chuckle at your code name.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to tweak one thing, Hans. I explained which number needs to be changed poorly. It's not the 100ths column, but the 1/100ths column (I hope I'm saying that right). So

    102.13 becomes 102.1C
    37.58 becomes 37.5H

    Etc.

    Sorry for the mix up.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Before I have a go at tweaking Hans code, can you clarify that this is what you want?

    [attachment=83754:ruin_numbers.GIF]
    Attached Images Attached Images

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, looks correct, thanks.

    I assumed that there was just one small thing in the code that needed to be changed to make it look at the correct number, but not being very VBA savvy, I wasn't sure what that "thing" was. Sorry. :-/

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts
    See the attached file with formula
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Satiria' post='775229' date='14-May-2009 19:46']Yes, looks correct, thanks.

    I assumed that there was just one small thing in the code that needed to be changed to make it look at the correct number, but not being very VBA savvy, I wasn't sure what that "thing" was. Sorry. :-/[/quote]

    I'm a VBA novice myself but I'm (enjoying) learning as I go along and most of that knowledge is gained here.

    As your requirement is the right-most character of the cell, I think the VBA would be much simpler than Hans original code, as this is much less complex (I think).

    This is what I have so far, but I am missing something, maybe Hans or another lounger will step into the rescue (or I will eventually figure it out)

    Code:
    Sub RuinNumbers2()
      Dim oCell As Range
      Dim strVal As String
      Dim c As String
      For Each oCell In Selection.Cells
    	strVal = oCell.Text
    		c = Right(strVal, 1)
    		Select Case c
    		  Case "0"
    			c = "}"
    		  Case "1"
    			c = "A"
    		  Case "2"
    			c = "B"
    		  Case "3"
    			c = "C"
    		  Case "4"
    			c = "D"
    		  Case "5"
    			c = "E"
    		  Case "6"
    			c = "F"
    		  Case "7"
    			c = "G"
    		  Case "8"
    			c = "H"
    		  Case "9"
    			c = "I"
    		End Select
    		Mid(strVal, Len(strVal), 1) = c
    		oCell.Value = strVal
    Next oCell
    End Sub
    Code edited to incorporate correction.

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Got it.... See the attached.

    [attachment=83760:Ruin_Numbers.xls]

    Hope this is what you are looking for.
    Attached Files Attached Files

Posting Permissions

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