Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 23rd 08, 02:16 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 19
Default 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


  #2   Report Post  
Old January 23rd 08, 02:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default 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

  #3   Report Post  
Old January 23rd 08, 02:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 19
Default 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

  #4   Report Post  
Old January 23rd 08, 03:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default 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



  #5   Report Post  
Old January 23rd 08, 03:24 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 19
Default 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



  #6   Report Post  
Old January 23rd 08, 06:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default 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 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



Reply
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 worksheets into separate workbooks Cobra Excel Discussion (Misc queries) 4 August 21st 12 03:29 AM
find a number between numbers in two separate columns confused about ranges Excel Worksheet Functions 2 December 30th 06 01:41 PM
separating +ve and -ve numbers into two columns Prospect Excel Discussion (Misc queries) 3 December 12th 06 12:47 PM
Separating words in a single cell ibere Excel Discussion (Misc queries) 11 June 15th 06 10:14 PM
separate whole words norika Excel Worksheet Functions 12 January 3rd 06 12:00 PM


All times are GMT +1. The time now is 01:09 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017