Thanks:  0
Likes:  0

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

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

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

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

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

7. no problem, glad to be of service.

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

## User Tag List

#### Posting Permissions

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