move text & numbers in column to separate columns

[deb]

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]

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

 

[David Adamson]

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

 

[Rick Rothstein]

These are a little bit shorter...

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

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


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

 

[Teethless mama]

Even shorter....

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

 

[Rick Rothstein]

Excellent... I like them!