Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CONSISTENT COVERSION OF A CELL (EXCEL)

    I Am working on a excel spread sheet which has a bunch of numbers seperated by slashes and dashes. I would like to convert the the values from 1 column of cells to a new consistent format. which looks like this
    000-00-00-000-00W0 There is 12 spots for numbers seperated by 4 dashes. 1 dash after three numbers, then 2 numbers and a dash, then two numbers and a dash then three numbers and a dash, then 2 numbers and a captial W and then two numbers. ex 000-00-00-000-00W00 . I have attached a spreadsheet to an example of what I am trying to accomplish. I did some of the conversion manually to show what I am trying to do. Have a look and let me know if there is a way to program a column to convert the values from another column into this format.

  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: CONSISTENT COVERSION OF A CELL (EXCEL)

    You would need some kind of custom function like this:

    <pre>Option Explicit
    Function ConvertString(vValue)
    Dim sTemp As String
    Dim iDash As Integer
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction
    With AWF
    sTemp = vValue
    sTemp = .Substitute(sTemp, "/", "-")
    sTemp = .Substitute(sTemp, " ", "")
    sTemp = .Substitute(sTemp, "-W", "-00W")
    iDash = Len(sTemp) - Len(.Substitute(sTemp, "-", ""))
    Select Case iDash
    Case 3
    sTemp = .Substitute(sTemp, "W", "-00W")
    Case 4
    Case Else
    ConvertString = "Not enough dashes"
    Exit Function
    End Select
    sTemp = .Rept("0", 4 - InStr(sTemp, "-")) & sTemp
    sTemp = .Replace(sTemp, 5, 0, .Rept("0", 7 - InStr(5, sTemp, "-")))
    sTemp = .Replace(sTemp, 8, 0, .Rept("0", 10 - InStr(8, sTemp, "-")))
    sTemp = .Replace(sTemp, 11, 0, .Rept("0", 14 - InStr(11, sTemp, "-")))
    End With
    ConvertString = Left(sTemp, 18)
    Set AWF = Nothing
    End Function</pre>


    Add it to a module and then use something like:
    <pre>=ConvertString(A1)</pre>


    There is probably other "validation" that should be done, but this gets all your cases you have here....
    Steve

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CONSISTENT COVERSION OF A CELL (EXCEL)

    thanks
    instead of Vvalue how would i set a loop so it takes A1 value changes it, then A2 ect.... and run through the operation till no more conversions are in column A

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

    Re: CONSISTENT COVERSION OF A CELL (EXCEL)

    Enter =ConvertString(A1) in cell B1, then fill down as far as needed, for example by double clicking the fill handle, i.e. the little black square in the lower right corner of B1.

    If you want to use code to change the values "in place" in column A itself:

    Sub ConvertValues()
    Dim oCell As Range
    For Each oCell in Range(Range("A1"), Range("A1").End(xlDown))
    oCell.Value = ConvertString(oCell.Value)
    Next oCell
    Set oCell = Nothing
    End Sub

    There should be no gaps in the list in column A.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CONSISTENT COVERSION OF A CELL (EXCEL)

    Could I build this into the same module or make a seperate one

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CONSISTENT COVERSION OF A CELL (EXCEL)

    Either way you want. Both will work.
    Legare Coleman

Posting Permissions

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