Converting string to number

Discussion in 'Microsoft Excel New Users' started by kenppy, Mar 5, 2010.

  1. kenppy

    kenppy Guest

    hi,

    I have a set of data that is to be cut and pasted from an external
    source.
    A typical cell reads '32813.58 sq mm', without the speech marks.
    The cell could be formatted before the pasting; but how do I strip off
    the units to leave the number only? The number could be any size.

    thanks
     
    kenppy, Mar 5, 2010
    #1
    1. Advertisements

  2. kenppy

    kenppy Guest

    This works but is still a string, how do I turn this to number?
    thanks
    says...
    > =LEFT(A1, LEN(A1)-5)
    >
    > Will return your data as required as long as the text to be stripped is
    > constant
    >
     
    kenppy, Mar 5, 2010
    #2
    1. Advertisements

  3. kenppy

    kenppy Guest

    The data is already loading into columns but the length of the stuff to
    strip may not be predicted, how would this be treated?
    thanks

    says...
    > Another alternative if you are pasting text into Excel is to use the text to
    > columns facility.
    >
    > You'll find this under data - text to columns. Ask again if you need more
    > help on this.
    >
     
    kenppy, Mar 5, 2010
    #3
  4. kenppy

    Bob Phillips Guest

    =--LEFT(A1, LEN(A1)-5)


    --

    HTH

    Bob

    "kenppy" <> wrote in message
    news:...
    >
    > This works but is still a string, how do I turn this to number?
    > thanks
    > says...
    >> =LEFT(A1, LEN(A1)-5)
    >>
    >> Will return your data as required as long as the text to be stripped is
    >> constant
    >>
     
    Bob Phillips, Mar 5, 2010
    #4
  5. kenppy

    Luke M Guest

    =VALUE(LEFT(A1, LEN(A1)-5))

    --
    Best Regards,

    Luke M
    "kenppy" <> wrote in message
    news:...
    >
    > This works but is still a string, how do I turn this to number?
    > thanks
    > says...
    >> =LEFT(A1, LEN(A1)-5)
    >>
    >> Will return your data as required as long as the text to be stripped is
    >> constant
    >>
     
    Luke M, Mar 5, 2010
    #5
  6. kenppy

    Luke M Guest

    Under Data - Text to Columns, choose delimited. Then choose space as your
    delimiter.

    --
    Best Regards,

    Luke M
    "kenppy" <> wrote in message
    news:...
    > The data is already loading into columns but the length of the stuff to
    > strip may not be predicted, how would this be treated?
    > thanks
    >
    > says...
    >> Another alternative if you are pasting text into Excel is to use the text
    >> to
    >> columns facility.
    >>
    >> You'll find this under data - text to columns. Ask again if you need
    >> more
    >> help on this.
    >>
     
    Luke M, Mar 5, 2010
    #6
  7. kenppy

    Guest Guest

    Thanks all of you, it's working now

    > Sorry try
    > =LEFT(A1, LEN(A1)-6)
    >
     
    Guest, Mar 5, 2010
    #7
    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. Rick
    Replies:
    3
    Views:
    626
  2. Guest

    Converting dates to a number

    Guest, Apr 26, 2005, in forum: Microsoft Excel New Users
    Replies:
    3
    Views:
    176
    Andy Wiggins
    Apr 27, 2005
  3. Dave Peterson

    Re: Converting number or text to a Date Format

    Dave Peterson, Oct 17, 2005, in forum: Microsoft Excel New Users
    Replies:
    0
    Views:
    223
    Dave Peterson
    Oct 17, 2005
  4. KipHartman

    Converting a calculated number into time

    KipHartman, Feb 24, 2006, in forum: Microsoft Excel New Users
    Replies:
    3
    Views:
    193
    Pete_UK
    Feb 24, 2006
  5. Walter

    replacing FIRST NUMBER-SECOND NUMBER with SECOND NUMBER

    Walter, Feb 23, 2008, in forum: Microsoft Excel New Users
    Replies:
    3
    Views:
    325
    Walter
    Feb 24, 2008
Loading...

Share This Page