Remove data from a text string in a single cell and place in - Page 2
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

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

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

    Default

     
    Wow! I can't believe how quick you people are! Sorry for my slow reply.
    Below are a few examples. I cut them out of Excel. Each item is in a single cell right now. These examples are typical. I have deleted most of the verbose descriptions. There is no uniformity to the length of the text. The item #s range from 3 to 5 digits, plus some are preceded or followed by one or more alpha characters. The price is not always the last part of the description.

    11111 Nothing makes....blah, blah... $7.95

    22222 A hummingbird couple sing....blah, blah... 6 1/8" x 4" x 5 5/8" high. $27.95

    3333 A bewitching...blah, blah... $29.95

    S25252 Sometimes even... Wood base. $29.95

    65123 This beautiful.... $49.95 Set of 3

    25139BLK A glistening..... $19.95 Set of 6

    Thanks anno & Rocky E! ANY additional help is very much appreciated!

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

    Default

    it is difficult to get the item number, is this always numeric? does it always appear at the beginning of the string?

    to get the price, assuming it always begins with a dollar sign and has two decimal places... if the test string is in cell F11, use the following formula...

    =MID(F11,FIND("$",F11),FIND(".",F11,FIND("$",F11))-FIND("$",F11)+3)

    ...sorry, know it is a bit nested

    <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>

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

    Default

    Thanks Daleyman! IT WORKS!!... It does put the price in the destination cell.. but I also need to remove the price from the text string. Any suggestions for this?

    Your question, is the item # always numeric.. unfortunately, no. About 95% ARE numeric (3 - 5 digits). The item number IS always at the beginning of the text string. We can deal with the alpha characters if we can just get the numbers sorted out.

    Thanks for any and all help!

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

    Default

    okay, looking at your sample again i am going to make an assumption that the item code is always at the beginning of the string and also it is always followed by a space character....

    string in cell F1

    ...cell G1 (as before)...
    =MID(F11,FIND("$",F11),FIND(".",F11,FIND("$",F11))-FIND("$",F11)+3)

    ...cell H1...
    =LEFT(F11,FIND(" ",F11)-1)

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


    ...phew! remember to fill these equations down to calculate for every row in your list. And when you're done COPY TO VALUES.




    <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>

  5. #15
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Friend,
    it is very simple while importing the file you can select the option which wizard shows.

    did not you got that wizard while opening the word file

    ni****h desai
    http://www.pexcel.com

  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,854
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    A 14-Apr-02 post.

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
  •  

 

 
DMCA.com