Help extracting string - 3 spaces from end of description

[MyDogJake]

Thanks in advance to any/all help!

I'd like to remove the last string of numbers (actually costs) from a description -post to a separate column -and then, re-combine with original description leaving off comments...

Example:

987654321 6 Snickersbars11/12/14/ DX,T 1.30 .60 3.60 --> desire "1.30 .60 3.60" for separate column
...and then.. re-combine with description such that the "comments; "DX,T" are left out.

Final result:
987654321 6 Snickersbars11/12/14/ 1.30 .60 3.60

Note: the forward slashes are always present and should be included in final result.

Thanks greatly!

PS: I've tried left, mid, rept, right text formulas but can't seem to nail down the spaces over issue?

 

[Rick Rothstein]

Yes! that works
...no there's just one more "tweak" ... I failed to notice that a "%" could be used in the costing information instead of a unit value... if a "%" is thrown in the mix the string would need to go to 4 spaces

example:
987654321 6 Snickersbars11/14/14 DT,X 3.12 5 % .94 ...in this case we'd want to extract "3.12 5 % .94"

Assuming that is the only place a % sign could appear in the cell, then this should work...

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",200)),600+200*ISNUMBER(FIND("%",A1))))

 

[MyDogJake]

Assuming that is the only place a % sign could appear in the cell, then this should work...

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",200)),600+200*ISNUMBER(FIND("%",A1))))

Most excellent! - thank you Rick

Q: would your formula still work if the % were anywhere else within the same area? .. (it shouldn't in my case but I'm thinking ahead as it could be a possibility.)

So; instead of the final string being: 1.50 5 % .30 7.20 ... what if it were 5 % 1.50 .30 7.20?

Lastly, should I put "SOLVED" somewhere in the subject line -and does the website take donations?

 

[Rick Rothstein]

Most excellent! - thank you Rick

Q: would your formula still work if the % were anywhere else within the same area? .. (it shouldn't in my case but I'm thinking ahead as it could be a possibility.)

So; instead of the final string being: 1.50 5 % .30 7.20 ... what if it were 5 % 1.50 .30 7.20?

Lastly, should I put "SOLVED" somewhere in the subject line -and does the website take donations?

When I said "Assuming that is the only place a % sign could appear in the cell...", I meant anywhere within the fields you wanted to display... what I was trying to imply is that if you had a % sign anywhere within the text, that occurrence would force the formula to return four trailing fields instead of three no matter what is in those four fields.

 

[Peter_SSs]

A few comments re Rick's formulas ..

1. It may not be an issue, but even for the standard data, the formula for the re-combining doesn't actually produce the expected results as shown in post #6. All the "/" characters are omitted except the last one.

2. IF it is possible that a % sign appears in the first part of the text and not among the numbers at the end, the initial extraction is wrong (cell B3 below).

3. IF it is possible that there are more or less than 3 "/" characters, the re-combining is not correct (cells C4:C5)

4. This would go against your description of what to do but IF there was a % sign among the numbers that was not preceded by a space the initial extraction would be wrong (cell B6)


In rows 9:14 I have suggested some modifications attempting to address each of the above issues.
Note that my sample data is not the same as that shown in post #6 as I was not able to copy from the image. For the future, you might look at the suggestions in my signature block below for ways to post copyable sample data directly into your post as I have done here. Makes it much easier for helpers if they don't have type a lot.

Excel Workbook
	A	B	C
1	074682103014 6 KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 1.98	2.89 .33 1.98	074682103014 6 KNUD JUICE APPLE 979348511 23 14 01/ 2.89 .33 1.98
2	074682103014 6% KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 % 1.98	2.89 .33 % 1.98	074682103014 6% KNUD JUICE APPLE 979348511 23 14 01/ 2.89 .33 % 1.98
3	074682103014 6 % KNUD JUICE APPLE 979348511/23/14 01/HD GBG 2.89 .33 1.98	GBG 2.89 .33 1.98	074682103014 6 % KNUD JUICE APPLE 979348511 23 14 01/ GBG 2.89 .33 1.98
4	074682103014/10 6 KNUD JUICE APPLE 979348511/23/14 01/HD GBG 2.89 .33 1.98	2.89 .33 1.98	074682103014 10 6 KNUD JUICE APPLE 979348511 23/ 2.89 .33 1.98
5	074682103014 6 KNUD JUICE APPLE 97934851123/14 01/HD GBG 2.89 .33 1.98	2.89 .33 1.98	074682103014 6 KNUD JUICE APPLE 97934851123 14 01 HD GBG 2.89 .33 1.98/ 2.89 .33 1.98
6	074682103014 6 KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33% 1.98	01/ 2.89 .33% 1.98	074682103014 6 KNUD JUICE APPLE 979348511 23 14 01/ 01/ 2.89 .33% 1.98
7
8
9	074682103014 6 KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 1.98	2.89 .33 1.98	074682103014 6 KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 1.98
10	074682103014 6% KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 % 1.98	2.89 .33 % 1.98	074682103014 6% KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 % 1.98
11	074682103014 6 % KNUD JUICE APPLE 979348511/23/14 01/HD GBG 2.89 .33 1.98	2.89 .33 1.98	074682103014 6 % KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 1.98
12	074682103014/10 6 KNUD JUICE APPLE 979348511/23/14 01/HD GBG 2.89 .33 1.98	2.89 .33 1.98	074682103014/10 6 KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33 1.98
13	074682103014 6 KNUD JUICE APPLE 97934851123/14 01/HD GBG 2.89 .33 1.98	2.89 .33 1.98	074682103014 6 KNUD JUICE APPLE 97934851123/14 01/ 2.89 .33 1.98
14	074682103014 6 KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33% 1.98	2.89 .33% 1.98	074682103014 6 KNUD JUICE APPLE 979348511/23/14 01/ 2.89 .33% 1.98
Extract & Combine

Lastly, should I put "SOLVED" somewhere in the subject line -and does the website take donations?

Solved: No, better solutions may come along later. (You can say it is solved, but we don't make anything permanent about that for the reason given)

Donations: No, this is a free forum.