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

[hisdwelling]

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]

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!

 

[daleyman]

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

 

[hisdwelling]

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!

 

[daleyman]

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.

 

[nisht]

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

 

[Aladin Akyurek]

A 14-Apr-02 post.