Separating numbers and words into separate columns
 

Sam Jones
5/9/2007 This info is all in one colum; how do I place the Sam
Jones dates in one column and the names in
Mary Smith another?
4/30/2007
Mary Smith

Separating numbers and words into separate columns
 

Hi

With data in column A, enter in B1
=IF(ISNUMBER(A1),A1,"")
and in C1
=IF(ISTEXTA1),A1,"")
Copy down as far as required

--

Regards
Roger Govier

"Windy" wrote in message
...

Sam Jones
5/9/2007 This info is all in one colum; how do I place the Sam
Jones dates in one column and the names in
Mary Smith another?
4/30/2007
Mary Smith

Separating numbers and words into separate columns
 

I can't thank you enough-once my fingers actually typed the formulas
correctly, it worked great!
Windy

"Roger Govier" wrote:

Hi

With data in column A, enter in B1
=IF(ISNUMBER(A1),A1,"")
and in C1
=IF(ISTEXTA1),A1,"")
Copy down as far as required

--

Regards
Roger Govier

"Windy" wrote in message
...

Sam Jones
5/9/2007 This info is all in one colum; how do I place the Sam
Jones dates in one column and the names in
Mary Smith another?
4/30/2007
Mary Smith

Separating numbers and words into separate columns
 

You oughtn't to type the formulae, just copy and paste them from here, and
then adjust any cell references accordingly. Retyping is too prone to
error.
--
David Biddulph

"Windy" wrote in message
...
I can't thank you enough-once my fingers actually typed the formulas
correctly, it worked great!
Windy

"Roger Govier" wrote:

Hi

With data in column A, enter in B1
=IF(ISNUMBER(A1),A1,"")
and in C1
=IF(ISTEXTA1),A1,"")
Copy down as far as required

--

Regards
Roger Govier

"Windy" wrote in message
...

Sam Jones
5/9/2007 This info is all in one colum; how do I place the Sam
Jones dates in one column and the names in
Mary Smith another?
4/30/2007
Mary Smith

Separating numbers and words into separate columns
 

Okay, I do have another question about this. I worked the formulas in my
spreadsheet and copied them down. Then I copied the columns and did a paste
special values to have the names in the column, not the formulas. Now I need
to copy the names into the blank spaces and it won't let me because Excel is
not finding "blanks" between the names because of the formula. So, now how
can I copy the names?

"Roger Govier" wrote:

Hi

With data in column A, enter in B1
=IF(ISNUMBER(A1),A1,"")
and in C1
=IF(ISTEXTA1),A1,"")
Copy down as far as required

--

Regards
Roger Govier

"Windy" wrote in message
...

Sam Jones
5/9/2007 This info is all in one colum; how do I place the Sam
Jones dates in one column and the names in
Mary Smith another?
4/30/2007
Mary Smith

Separating numbers and words into separate columns
 

Hi

It wasn't your fingers that were incorrect, it was my posting.
I missed an opening parenthesis after the word TEXT in the second formula.
Obviously you corrected that.

If you are saying you want the Names lined up with the dates in adjacent
columns, then place cursor in cell C1 and right clickInsertShift cells
down.
That will align your data.

An easy way to get rid of the blank rows in between your data, is to mark
the columns B and C (after your Paste Special), then DataSortNo header
rowColumn CAscending and all of the blank rows will "fall to the bottom".

If the order of your data needs to be preserved, then before carrying out
the above, Enter 1 in cell D1, 2in cell D2. Mark both cells and grab the
fill handle (small black cross at bottom right of cell D2) and drag down as
far as required.
Include column D in your sort.
Delete all the "surplus" rows (i.e. those with just a number in column D and
nothing in B or C), then Sort columns B:D again, this time by column D and
your data will be back in the original order. Then delete column D
--

Regards
Roger Govier

"Windy" wrote in message
...
Okay, I do have another question about this. I worked the formulas in my
spreadsheet and copied them down. Then I copied the columns and did a
paste
special values to have the names in the column, not the formulas. Now I
need
to copy the names into the blank spaces and it won't let me because Excel
is
not finding "blanks" between the names because of the formula. So, now
how
can I copy the names?

"Roger Govier" wrote:

Hi

With data in column A, enter in B1
=IF(ISNUMBER(A1),A1,"")
and in C1
=IF(ISTEXTA1),A1,"")
Copy down as far as required

--

Regards
Roger Govier

"Windy" wrote in message
...

Sam Jones
5/9/2007 This info is all in one colum; how do I place the Sam
Jones dates in one column and the names in
Mary Smith another?
4/30/2007
Mary Smith