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

hisdwelling

New Member
Joined
Apr 10, 2002
Messages
16
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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"
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top