My cell A1 contains:
65.5,54,0,,,,,,,65.5,57.5,0,,,,,,72,51,0,,,,,,,,37.5,43.75,0,,,,,,43.75,72,0,,,,,,65.5,56,0,,,,,,
OBJECTIVE:
Using formulas in CELLS A2:A9, these same cells are populated as follows:
A
2 65.500
3 54.000
4 57.500
5 72.000
6 51.000
7 37.500
8 43.750
9 56.000
Its 4 things I need done w/ a single function in each cell:
(1) text function to parse out the values,
(2) kill the commas,
(3) eliminate the duplicates
(4) eliminate the ZERO values.
Any Help? THANKS!!!
Last edited by James C; 03-01-2013 at 11:53 AM. Reason: trying to polish the location of that 'A' in the line above "2 65.500"
You have 7 commas after the first zero, then 6, then 8, then 6, 6, 6 - are these all meant to be the same?
Pete
No, the feeder cell will vary and be a mess; random solo zero and non-zero values and commas throughout.
I need the zero values and commas eliminated, each unique value placed in its own cell in the specified range, and actually, my mistake, the non-zero dupe values must be preserved, thus the designated destination range should be longer, its needs to be A2:A55
THANKS!
JIM
1) Format A2:A??? with a number format of 0.000
2) In B1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1, ",,,",","),",,",","),",,",","),",0,",",")
3) In A2: =LEFT($B$1, FIND(",", $B$1)-1)+0
4) In A3: =LEFT(SUBSTITUTE(MID($B$1, FIND("^", SUBSTITUTE($B$1, ",", "^", ROW(A1)))+1, 5),",",REPT(" ",20)),20)+0
5) Copy A3 down til the errors appear meaning the data is done.
Last edited by JBeaucaire; 03-01-2013 at 04:49 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
not quite there, but close, thanks
to_forum.xlsxB2 needs attention...
see attached
You didn't notice (nor did I) that formula is looking at B2? Change that to B1.
Jerry,
Great that fixed it!
NOW, it turns out that in my subsequent actual APPLICATION of your excellent solution, my feed data is not in A1; rather A1 contains a formula that draws from a bunch of other cells. Is there a way, if you will, that you can parse A1 for its output, not its formula? Placeholder cell(s) w/ formulas are fine, but I prefer no macros.
Thanks again!
JIM
It doesn't matter if A1 result is due to a formula. B1 will still work.
Jerry,
After concentrating on your formulas, I am finally able to see your solution is perfect, thank you.
Now, I'm having trouble adapting the formula to where I need the INPUT DATA.
See the attached to_forum_3.xlsx.
Furthermore, I am doing a "STEREO" implementation, one LEFT of line between AC and AD. Other, on the RIGHT.
Everything is perfect in this spreadsheet, except the formula in AC5 and AD5.
Can you tweak each for me?
THANKS AGAIN, VERY VERY MUCH.
JIM
Like so, the ROW(A1) is "code" for "the first comma", so it should be A1 in the first cell it is used...
AC5: =IFERROR(LEFT(SUBSTITUTE(MID($AA$65, FIND("^", SUBSTITUTE($AA$65, ",", "^", ROW(A1)))+1, 5),",",REPT(" ",20)),20)+0, "")
AD5: =IFERROR(LEFT(SUBSTITUTE(MID($AD$65, FIND("^", SUBSTITUTE($AD$65, ",", "^", ROW(A1)))+1, 5),",",REPT(" ",20)),20)+0, "")
Re: text formula to parse a cell, clean it, and dump into a fixed CELL RANGE - NO MACROS
Sorry Jerry,
need to take a step back here
lets say I have cell A1
its contents:
65.5,54,0,-12.1,0,0,0,0,0,65.5,57.5,0,0,0,0,0,0,0,72,51,0,0,0,0,0,0,0,37.5,37.5,43.75,0,0,0,0,0,0,43.75,72,0,0,0,0,0,0,0,65.5,56,0,0,0,0,0,0,0
I want cell A2 to be a formula that outputs the contents of A1 just with zero and negative values omitted, thus:
65.5,54,65.5,57.5,72,51,37.5,37.5,43.75,43.75,72,65.5,56
Thanks.
JIM
You can use SUBSTITUTE() to replace known strings with other strings, or with nothing. So eliminating double or triple commas, or removing ,0 is pretty easy. Tht AA65 formula would change to:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($AA$64,",0",""),",,,",","),",,",","),",,",","),",0,",",")
You also need to copy that AC5 formula down for the display to be correct.
As for removing all the negative? I'm going to stop "formulating" here. If I were faced with your project, I would have created a new function long ago and been using it. With this new addition it's a UDF requirement in my opinion.
The original question for this post has been resolved and you're evolving into something more.
If you're up for a VBA answer, I could give a you a UDF to install in your workbook and this would be easy from there. If so, close this thread, head over the to the VBA forum and post a thread there. Send me a Private Message with a link to that thread and I'll join over there.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks