Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Remove data from a text string in a single cell and place in

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Daleyman, you're amazing!!!!

    The first 2 formulas work just right!
    The 3rd formula works just right... as long as there is text after the price in the original string. If the price is last in the text string, I get a #VALUE! error.

    Will the postal service let us send chocolate overseas?


  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmmm... my reply above may not be correct. Below are 2 examples. They both have the price last. The 3rd formula worked correctly on one, but not on the other...


    String: (this one errors)
    65924 Classically styled... blah, blah.... 6 1/4" x 2 1/4" x 1" high. $7.95

    Results: $7.95
    65924
    #VALUE!

    String: (this one works)
    45617 Kids love to play.... blah, blah. 22" x 7 1/2" x 1 1/2" $9.95

    Results: $9.95
    45617
    Kids love to play.... blah, blah. 22" x 7 1/2" x 1 1/2"

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hmmm, thats curious, just tested these examples and they work for me. Or at least the first two do, the third is missing its price. Please check that you are pluggin in the equations correctly, remember the third refers to both of the other two equations.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK... after playing with my data for a while, I know that the strings where the price is followed by one or more spaces are the strings where the formula works.

    So, where the formula returns #VALUE!, I need to go in and add a space after the price.

    Thanks for all your help!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ah, i see the error, sorry.... last equation should read...

    =MID(F11,LEN(H11)+2,FIND("$",F11)-LEN(H11)-2) & " " & RIGHT(F11,LEN(F11)-(FIND("$",F11)+LEN(G11)-1))


    ....if you want to change your existing equation without having to plug it in all over again, i just changed the end from ))) to )-1))

    hope this is better.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    AWESOME!!! Thank you so much. You saved me HOURS & HOURS of editing!

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no problem, glad to be of service.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Our supplier gives us a text string (In Word) that includes the item #, the description and the price. We have imported the text strings into an Excel workbook. In order to upload this to our website, I need to remove the item # and price, and place each into a separate cell, in the correct place in another workbook. Is there an easy way to do this? (We are not VSB saavy)

    Any ideas will be very much appreciated!

    [ This Message was edited by: hisdwelling on 2002-04-11 19:00 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    have a look at text to columns (data menu). it won't be that useful if the strings you want to separate differ in length though. if you can give a sample string or strings someone should be able to come up with a solution

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you are asking for a parser macro. If the data has consistent character spacing you can brute force it with the MID$() function. If you can describe the data or even give us 10 rows we can help a lot more.

    Rocky...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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