LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Emulating Text to Columns -- Separating Data into Appropriate Colu

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
separating data in columns tee Excel Worksheet Functions 2 October 27th 06 01:59 PM
separating data into multiple columns AsaReynolds New Users to Excel 1 August 21st 06 11:07 PM
Separating Data Into Multiple Columns penroma Excel Discussion (Misc queries) 3 May 26th 06 08:24 PM
Separating text and data in a column Amber Excel Worksheet Functions 1 April 4th 06 11:42 PM
Coverting data from some columns to rows (but not the primary colu sjn Excel Discussion (Misc queries) 5 January 16th 06 11:02 PM


All times are GMT +1. The time now is 03:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"