move text & numbers in column to separate columns

Discussion in 'Microsoft Excel Misc' started by deb, Jan 16, 2009.

  1. deb

    deb Guest

    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?
     
    deb, Jan 16, 2009
    #1
    1. Advertisements

  2. deb

    Pete_UK Guest

    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?
     
    Pete_UK, Jan 16, 2009
    #2
    1. Advertisements

  3. 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?
    >
    >
    >
     
    David Adamson, Jan 16, 2009
    #3
  4. 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
    news:...
    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?
     
    Rick Rothstein, Jan 16, 2009
    #4
  5. 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
    > news:...
    > 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?

    >
    >
     
    Teethless mama, Jan 16, 2009
    #5
  6. Excellent... I like them!

    --
    Rick (MVP - Excel)


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

    >>
    >>
     
    Rick Rothstein, Jan 16, 2009
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mshroff
    Replies:
    2
    Views:
    416
    Peter Atherton
    Dec 11, 2003
  2. Guest
    Replies:
    12
    Views:
    306
    Guest
    Feb 16, 2006
  3. Guest
    Replies:
    1
    Views:
    175
    Guest
    Aug 5, 2006
  4. Tacrier
    Replies:
    3
    Views:
    268
    Sean Timmons
    Oct 13, 2008
  5. Zorro

    Move (print) text column on a separate row

    Zorro, Mar 18, 2009, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    176
    Zorro
    Mar 18, 2009
Loading...

Share This Page