Forum Discussion
Category seperation
Put this formula into cell B3 (after inserting an empty column B) and copy it down.
=IF(AND(ISNUMBER(A3),ISTEXT(A2)),A2,B2)
It works because fortunately the fields in Column A with the colon are text, whereas the others are numbers. What the logic is saying is
If the cell to the immediate left of the current cell (B3 at the start) is a number and the cell right above it is text, then put that text here in B3 (at the start). If, on the other hand, the cell to the immediate left is a number and the cell above is a number also, then repeat what is in Column B immediately above the current cell.
Then when, many rows later it suddenly encounters another text, it changes to that new text and continues with it.
It's not a perfect solution, but should serve.
Just out of curiosity, what does that data reflect?
For future reference, you really didn't need to upload that entire file; it is over 24 meg in size. It's good to have an actual file, but it could have been a representative subset and saved everybody a lot of bandwidth. That's why I'm only giving you the formula back.
You say, "somewhat like" in your opening statement...which isn't as precise as a computer would need to be, and you're asking for a computer resolution.
It's also not clear whether that layout you give as the desired "clean" outcome is one column or two. If one, is the spacing a part of the desired outcome as well?
A few other questions:
- Where does this data come from in the first place?
- Is this a one-time clean up or are you going to need to be doing this on a regular basis?
- Is it possible to fix the "mess" at an earlier stage in the process--again, back to wherever it comes from?
- Is it possible for you to post the actual spreadsheet in which this column appears? That would be most helpful to anyone trying to help you.
Can you post the actual file? If not, something that is either a subset of it or an accurate representation of it?
Without that, one can only take stabs at what the resolution would actually be.
Yes. I have attached the file with a same example for a short data below. mathetes
Raw data:
Required data:
Put this formula into cell B3 (after inserting an empty column B) and copy it down.
=IF(AND(ISNUMBER(A3),ISTEXT(A2)),A2,B2)
It works because fortunately the fields in Column A with the colon are text, whereas the others are numbers. What the logic is saying is
If the cell to the immediate left of the current cell (B3 at the start) is a number and the cell right above it is text, then put that text here in B3 (at the start). If, on the other hand, the cell to the immediate left is a number and the cell above is a number also, then repeat what is in Column B immediately above the current cell.
Then when, many rows later it suddenly encounters another text, it changes to that new text and continues with it.
It's not a perfect solution, but should serve.
Just out of curiosity, what does that data reflect?
For future reference, you really didn't need to upload that entire file; it is over 24 meg in size. It's good to have an actual file, but it could have been a representative subset and saved everybody a lot of bandwidth. That's why I'm only giving you the formula back.