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

hisdwelling · Jan 14th, 2002, 12:38 PM

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?

hisdwelling · Jan 14th, 2002, 12:52 PM

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"

daleyman · Jan 14th, 2002, 01:09 PM

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.

hisdwelling · Jan 14th, 2002, 01:14 PM

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!

daleyman · Jan 14th, 2002, 01:23 PM

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.

hisdwelling · Jan 14th, 2002, 01:31 PM

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

daleyman · Jan 14th, 2002, 03:21 PM

no problem, glad to be of service.

hisdwelling · Apr 11th, 2002, 07:57 PM

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 ]

anno · Apr 11th, 2002, 08:06 PM

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

Rocky E · Apr 11th, 2002, 09:14 PM

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

Jan 14th, 2002, 01:09 PM	#3
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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>

Jan 14th, 2002, 01:23 PM	#5
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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>

Jan 14th, 2002, 03:21 PM	#7
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Jan 14th, 2002, 12:38 PM	#1
hisdwelling New Member Join Date: Apr 2002 Posts: 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?

Jan 14th, 2002, 12:52 PM	#2
hisdwelling New Member Join Date: Apr 2002 Posts: 16	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"

Jan 14th, 2002, 01:14 PM	#4
hisdwelling New Member Join Date: Apr 2002 Posts: 16	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!

Jan 14th, 2002, 01:31 PM	#6
hisdwelling New Member Join Date: Apr 2002 Posts: 16	AWESOME!!! Thank you so much. You saved me HOURS & HOURS of editing!

Apr 11th, 2002, 07:57 PM	#8
hisdwelling New Member Join Date: Apr 2002 Posts: 16	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 ]

Apr 11th, 2002, 08:06 PM	#9
anno Board Regular Join Date: Feb 2002 Posts: 202	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

Apr 11th, 2002, 09:14 PM	#10
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	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...