Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi,
I am working with an extremely large amount of data, and "Text to Columns" just isn't cutting it (read: enormous, jumbled mass). Specifically, I need to separate (first by spaces, then by commas, dashes, semi-colons, etc.), the columns I have into a series of others that are both meaningful and easier to manage. As an example, I have the following: ********* ***** ** ****** ** ********* ***** *** ** ****** ****** ****** **** **** ***** ** ****** *** *** ** **** ** *** ****** ********** **** *** ****** ***** ******* **** What I'd like to do is to separate the items that end with a "space, then two characters" format (irrespective of how many other spaces are in the string), into their appropriate columns, and then those that end in three, four, etc. -- Unfortunately, they should never have been in the same column in the first place. Through my own knowledge of Excel (albeit somewhat limited), and from reading other posts here, I've been able to come up with individual formulas (using mid, len, find) that will separate out the "twos" with one space, and with two (but not yet with three, etc., although I suspect if I keep working I should be able to), but what I really want is one that will move them all at once. Right now, I get a value error for all strings that have two spaces when I use the formula for one, as well as when I use the formula for two on those that only have one (or three, etc.). Is there any way that I might be able to use a conditional statement which counts the number of spaces, and performs the correct calculations appropriately? I'm sure there must be, but I honestly can't think of anything. Is there anyone out there who can help (preferably without using a macro)? Thanks, I'd really appreciate it. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
separating data in columns | Excel Worksheet Functions | |||
separating data into multiple columns | New Users to Excel | |||
Separating Data Into Multiple Columns | Excel Discussion (Misc queries) | |||
Separating text and data in a column | Excel Worksheet Functions | |||
Coverting data from some columns to rows (but not the primary colu | Excel Discussion (Misc queries) |