A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Separating numbers and words into separate columns



 
 
Thread Tools Display Modes
  #1  
Old January 23rd 08, 02:16 AM posted to microsoft.public.excel.misc
Windy
external usenet poster
 
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

Ads
  #2  
Old January 23rd 08, 02:27 AM posted to microsoft.public.excel.misc
Roger Govier[_3_]
external usenet poster
 
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  
Old January 23rd 08, 02:42 PM posted to microsoft.public.excel.misc
Windy
external usenet poster
 
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  
Old January 23rd 08, 03:18 PM posted to microsoft.public.excel.misc
David Biddulph[_2_]
external usenet poster
 
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  
Old January 23rd 08, 03:24 PM posted to microsoft.public.excel.misc
Windy
external usenet poster
 
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  
Old January 23rd 08, 06:32 PM posted to microsoft.public.excel.misc
Roger Govier[_3_]
external usenet poster
 
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 click>Insert>Shift 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 Data>Sort>No header
row>Column C>Ascending 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
>> >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 12:44 AM.


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