PC Review


Reply
Thread Tools Rate Thread

move text & numbers in column to separate columns

 
 
deb
Guest
Posts: n/a
 
      16th Jan 2009
I have this information in the same column:
ACER
146555
THOMAS
188223

There are some blank lines in between - I need all the text in one column
and all the numbers in another but I need them to stay on the same row they
are on - can anyone help with this?



 
Reply With Quote
 
 
 
 
Pete_UK
Guest
Posts: n/a
 
      16th Jan 2009
Let's say you want the numbers in column B - put this in B1:

=IF(A1="","",IF(ISNUMBER(A1),A1,""))

and this in C1:

=IF(A1="","",IF(ISNUMBER(A1),"",A1))

then copy these two formulae down.

Hope this helps.

Pete

On Jan 16, 1:12*am, deb <(E-Mail Removed)> wrote:
> I have this information in the same column:
> ACER
> 146555
> THOMAS
> 188223
>
> There are some blank lines in between - I need all the text in one column
> and all the numbers in another but I need them to stay on the same row they
> are on - can anyone help with this?


 
Reply With Quote
 
 
 
 
David Adamson
Guest
Posts: n/a
 
      16th Jan 2009
Try this

Sub move_numbers()
Dim i As Integer
Dim Check As Variant
Dim result As Variant

For i = 1 To 100
With Worksheets("name")
Check = .Range("a2").Offset(i, o)
result = IsNumeric(Check)

If result = "True" Then
.Range("a2").Offset(i, 1) = Check
.Range("a2").Offset(i, 0) = ""
End If
End With
Next i

End Sub



deb wrote:
> I have this information in the same column:
> ACER
> 146555
> THOMAS
> 188223
>
> There are some blank lines in between - I need all the text in one column
> and all the numbers in another but I need them to stay on the same row they
> are on - can anyone help with this?
>
>
>

 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      16th Jan 2009
These are a little bit shorter...

For Text: =IF(ISNUMBER(-A1),"",A1)

For Numbers: =IF(COUNT(A1)=1,A1,"")
--
Rick (MVP - Excel)


"Pete_UK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
Let's say you want the numbers in column B - put this in B1:

=IF(A1="","",IF(ISNUMBER(A1),A1,""))

and this in C1:

=IF(A1="","",IF(ISNUMBER(A1),"",A1))

then copy these two formulae down.

Hope this helps.

Pete

On Jan 16, 1:12 am, deb <(E-Mail Removed)> wrote:
> I have this information in the same column:
> ACER
> 146555
> THOMAS
> 188223
>
> There are some blank lines in between - I need all the text in one column
> and all the numbers in another but I need them to stay on the same row
> they
> are on - can anyone help with this?


 
Reply With Quote
 
Teethless mama
Guest
Posts: n/a
 
      16th Jan 2009
Even shorter....

For Text: =T(A1)
For Number: =IF(N(A1),A1,"")


"Rick Rothstein" wrote:

> These are a little bit shorter...
>
> For Text: =IF(ISNUMBER(-A1),"",A1)
>
> For Numbers: =IF(COUNT(A1)=1,A1,"")
> --
> Rick (MVP - Excel)
>
>
> "Pete_UK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> Let's say you want the numbers in column B - put this in B1:
>
> =IF(A1="","",IF(ISNUMBER(A1),A1,""))
>
> and this in C1:
>
> =IF(A1="","",IF(ISNUMBER(A1),"",A1))
>
> then copy these two formulae down.
>
> Hope this helps.
>
> Pete
>
> On Jan 16, 1:12 am, deb <(E-Mail Removed)> wrote:
> > I have this information in the same column:
> > ACER
> > 146555
> > THOMAS
> > 188223
> >
> > There are some blank lines in between - I need all the text in one column
> > and all the numbers in another but I need them to stay on the same row
> > they
> > are on - can anyone help with this?

>
>

 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      16th Jan 2009
Excellent... I like them!

--
Rick (MVP - Excel)


"Teethless mama" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Even shorter....
>
> For Text: =T(A1)
> For Number: =IF(N(A1),A1,"")
>
>
> "Rick Rothstein" wrote:
>
>> These are a little bit shorter...
>>
>> For Text: =IF(ISNUMBER(-A1),"",A1)
>>
>> For Numbers: =IF(COUNT(A1)=1,A1,"")
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Pete_UK" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> Let's say you want the numbers in column B - put this in B1:
>>
>> =IF(A1="","",IF(ISNUMBER(A1),A1,""))
>>
>> and this in C1:
>>
>> =IF(A1="","",IF(ISNUMBER(A1),"",A1))
>>
>> then copy these two formulae down.
>>
>> Hope this helps.
>>
>> Pete
>>
>> On Jan 16, 1:12 am, deb <(E-Mail Removed)> wrote:
>> > I have this information in the same column:
>> > ACER
>> > 146555
>> > THOMAS
>> > 188223
>> >
>> > There are some blank lines in between - I need all the text in one
>> > column
>> > and all the numbers in another but I need them to stay on the same row
>> > they
>> > are on - can anyone help with this?

>>
>>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Parsing Column of Numbers into 5 separate columns Dkline Microsoft Excel Worksheet Functions 1 13th Feb 2009 04:15 PM
separate multiline address into separate columns -Dman100- Microsoft Excel Programming 3 29th Oct 2008 02:42 AM
I wish to separate city, state, and zip into 3 separate columns =?Utf-8?B?Qm9iIFdvb2xiZXJ0?= Microsoft Excel Worksheet Functions 1 11th Jul 2006 05:29 PM
HELP to separate 5 numbers into 5 separate columns =?Utf-8?B?d2lsbGlhbXNi?= Microsoft Access External Data 13 5th May 2005 05:44 AM
Need formula to add numbers that meet two separate criteria from two separate columns mshroff Microsoft Excel Misc 2 11th Dec 2003 09:39 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:25 PM.