# 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?

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?

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?
>
>
>

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?

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?

>
>

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?

>>
>>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 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 OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Dkline Microsoft Excel Worksheet Functions 1 13th Feb 2009 04:15 PM -Dman100- Microsoft Excel Programming 3 29th Oct 2008 02:42 AM =?Utf-8?B?Qm9iIFdvb2xiZXJ0?= Microsoft Excel Worksheet Functions 1 11th Jul 2006 05:29 PM =?Utf-8?B?d2lsbGlhbXNi?= Microsoft Access External Data 13 5th May 2005 05:44 AM mshroff Microsoft Excel Misc 2 11th Dec 2003 09:39 AM

Features