Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing cell (excel 2000)

    Someone gave me a worksheet with a number of cells that have hard-coded numbers in them, eg. 5445.33+224.77-44+65. Lots of numbers. Now, I need to parse them out (with their proper sign) so that I can show the detail. Since there are multiple numbers, I am thinking about some kind of miracle macro that will put the string into a variable, and then maybe do/while len(var)>0, find the next "+224.77" , strip it out of the variable, write it to a new cell as a value, then save the now-shorter string back to the variable again, and start the process anew.
    Does this make sense? Can someone suggest a basic set of code to do this. I'll bet there are plenty of users that might find it useful.
    Thanks.

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

    Re: Parsing cell (excel 2000)

    Are they in one column each with a leading = sign? If so, Find-&-Replace the leading = sign with a text character, then use Data, Text-to-Columns.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Parsing cell (excel 2000)

    Does this do what you want? For production work, you would need to add error handling.

    Sub ParseCells()
    Dim oCell As Range
    Dim lngOffset As Long
    Dim lngOldPos As Long
    Dim lngPos As Long
    Dim strValue As String
    Dim strTemp As String

    For Each oCell In Selection
    strValue = oCell.Value
    If Not strValue = "" Then
    lngOldPos = 1
    lngOffset = 1
    For lngPos = 1 To Len(strValue)
    If InStr("+-", Mid(strValue, lngPos, 1)) > 0 Then
    If lngPos > lngOldPos Then
    oCell.Offset(0, lngOffset).Value = _
    CDbl(Mid(strValue, lngOldPos, lngPos - lngOldPos))
    lngOldPos = lngPos
    lngOffset = lngOffset + 1
    End If
    End If
    Next lngPos
    oCell.Offset(0, lngOffset).Value = _
    CDbl(Mid(strValue, lngOldPos))
    End If
    Next oCell

    Set oCell = Nothing
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing cell (excel 2000)

    They are in one cell as =404.52-4233.55+17+15.22-357.75, and so on.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing cell (excel 2000)

    I copied this as-is and selected one cell to be parsed. Running it, all i got was the cell value as one number, next to the original.

  6. #6
    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: Parsing cell (excel 2000)

    Try changing the line:
    strValue = oCell.Value

    To:
    strValue = Mid(oCell.Formula, 2)

    Steve

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing cell (excel 2000)

    That did the trick. Thanks. I will print this out and study it. That's a big help.

Posting Permissions

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