Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Special Parsing Methods (2002)

    Greetings,

    I am in need of a macro to parse out some information that was obtained via OCR, here's how this information is formatted:

    i,# Z Blue Diamond Almonds Available in 12-pack, or 24 pack individual case ......................$29.95

    All of the entries are bold initially, then plain for the description, and the price is extended out with dots. I would like to break each of these three into three different columns on the same row. The list is alphabetized, meaning that products are listed in alphabetical order, however there is junk characters before the actual listing, represented here by i,#Z. I would like the macro to prompt me for the starting character, in this case 'B', isolate the bold part of that string, place that in column a. then move the plain to column b, and drop the price in column c. Any help you can provide would be greatly appreciated! I don't mind building this myself, however I am uncertain of which functions might be required.

    Thanks Guys, I really appreciate it!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  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: Special Parsing Methods (2002)

    Kel, you might be able to do this with some creative Find&Replace, if you only need to do it once. Are the junk characters always the same length, and does the data always include "Available in ..."?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    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: Special Parsing Methods (2002)

    I don't have time to work anything up tonite, some thoughts:

    FInd the dollar sign (INSTR) to get the cost (with Mid)

    Find multiple periods (instr) to find the ".." to determine the end for the description

    To find the end of the bold will require looking at each char and finding the first whose bold property is set to false. You can do it individually or create some search alogrithms (if desired) to speed it up (start in middle, then cut in half, etc etc.)

    Steve

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Parsing Methods (2002)

    Unfortunately, the junk at the beginning isn't always the same length, in some cases, the junk isn't there at all. (hence the need to prompt for the first useable character in the bold string). I don't think find and replace will work for me, but I will look into that. Also, because it's OCR, it doesn't always include 'Available In..' either. It's a mess alright.. Thanks for your help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Special Parsing Methods (2002)

    Here is a custom array function that expects the cell with the information as first argument, and the starting character as second argument. It will return the three parts in three consecutive cells.

    Function Split3(oCell As Range, FirstChar As String)
    Dim iBound1 As Integer
    Dim iBound2 As Integer
    Dim iBound3 As Integer
    Dim iBound4 As Integer
    Dim strPart1 As String
    Dim strPart2 As String
    Dim dblPart3 As Double

    iBound1 = InStr(oCell.Value, FirstChar)
    If iBound1 = 0 Then
    iBound1 = 1
    End If

    iBound2 = iBound1
    Do While oCell.Characters(iBound2, 1).Font.Bold = True
    iBound2 = iBound2 + 1
    Loop

    iBound3 = InStr(iBound2, oCell.Value, ".")
    iBound4 = InStr(iBound3, oCell.Value, "$")

    strPart1 = Trim(Mid(oCell.Value, iBound1, iBound2 - iBound1))
    strPart2 = Trim(Mid(oCell.Value, iBound2, iBound3 - iBound2))
    dblPart3 = CDbl(Mid(oCell.Value, iBound4 + 1))

    Split3 = Array(strPart1, strPart2, dblPart3)
    End Function

    See the attached workbook. To enter the formula, I selected C1:E1, entered =Split3(A1,B1) and used Ctrl+Shift+Enter to confirm, since it is an array formula.

    Note: because I am using a non-US system, you may have to recalculate to see the correct price.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Parsing Methods (2002)

    Thanks Millions Hans, however when I hit CTRL+SHIFT+ENTER, all three columns return #NAME?.. any ideas? [img]/forums/images/smilies/smile.gif[/img]
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Special Parsing Methods (2002)

    Getting the junk characters out will be tricky, or manual. I have commitments tonight, but using my "lastbold" function try something like this:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>1</td><td>1#hdf Blue Diamond blah blah .. $2.65</td><td>=TRIM(LEFT(A1,(lastbold(A1))))</td><td>=SUBSTITUTE(MID(A1,lastbold(A1),FIND("$",A1 ,1)-lastbold(A1)),".","")</td><td align=right>=RIGHT(A1,LEN(A1)-FIND("$",A1,2)+1)</td></table>
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Special Parsing Methods (2002)

    Does this happen in my sample workbook, or in your own workbook?

    If in your own workbook: did you copy the code into the workbook? If not, you must prefix the name of the function in the formula with the name of the workbook containing the code, for example

    ='Kel.xls'!Split3(A1,B1)

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Parsing Methods (2002)

    This occurs in your workbook, and also in the one containing the information... do I need to activate the function somehow?

    Thanks again for all your help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Special Parsing Methods (2002)

    If it also occurs in my workbook, activate the Visual Basic Editor (Alt+F11), then select Debug | Compile VBAProject. Do you get an error message?
    If so, what is the error?
    If not, does the function work now?

    If other Loungers have looked at this, what do they experience?

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

    Re: Special Parsing Methods (2002)

    It works fine here.
    Legare Coleman

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Parsing Methods (2002)

    Ahh, macro security was preventing it from running, however there is another obstacle or two, and I can't seem to figure this one out...

    In some cases, the price does not have a $ sign. there are far too many of these to manually enter them, so I'm hoping your code can work around this.. Also, in some cases, the junk field will start with a <,>,=,+,-.. or other math derivative, seriously confusing excel.. any ideas here?

    Thanks again for all your help on this! [img]/forums/images/smilies/smile.gif[/img]
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Special Parsing Methods (2002)

    Thanks for testing! So the function works OK in my Dutch language version of Excel 2002, and in your English language Excel 2000. What could cause it to fail in Kel's English language Excel 2002? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special Parsing Methods (2002)

    Since it's OCR, it's also possible that the price contains any number of extraneous characters, I would like for this whole string to be separated, whether it's formatted properly or not.... [img]/forums/images/smilies/smile.gif[/img] Thanks again!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Special Parsing Methods (2002)

    1. If you format the column that will contain the formatted values as text, it shouldn't matter if the value starts with + or something like that.

    2. Here is a modified version of the function. It does not search for $. It'll return an error if the part after the dots is not a number or a dollar amount.

    Function Split3(oCell As Range, FirstChar As String)
    Dim iBound1 As Integer
    Dim iBound2 As Integer
    Dim iBound3 As Integer
    Dim iBound4 As Integer
    Dim strPart1 As String
    Dim strPart2 As String
    Dim dblPart3 As Double

    iBound1 = InStr(oCell.Value, FirstChar)
    If iBound1 = 0 Then
    iBound1 = 1
    End If

    iBound2 = iBound1
    Do While oCell.Characters(iBound2, 1).Font.Bold = True
    iBound2 = iBound2 + 1
    Loop

    iBound3 = InStr(iBound2, oCell.Value, ".")
    iBound4 = iBound3
    Do While oCell.Characters(iBound4, 1).Text = "."
    iBound4 = iBound4 + 1
    Loop

    strPart1 = Trim(Mid(oCell.Value, iBound1, iBound2 - iBound1))
    strPart2 = Trim(Mid(oCell.Value, iBound2, iBound3 - iBound2))
    dblPart3 = CDbl(Trim(Mid(oCell.Value, iBound4)))

    Split3 = Array(strPart1, strPart2, dblPart3)
    End Function

Page 1 of 2 12 LastLast

Posting Permissions

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