move text & numbers in column to separate columns
 

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?

move text & numbers in column to separate columns
 

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

move text & numbers in column to separate columns
 

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?

move text & numbers in column to separate columns
 

These are a little bit shorter...

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

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


"Pete_UK" wrote in message
...
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 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?

move text & numbers in column to separate columns
 

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" wrote in message
...
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 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?

move text & numbers in column to separate columns
 

Excellent... I like them!

--
Rick (MVP - Excel)


"Teethless mama" wrote in message
...
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" wrote in message
...
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 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?