Results 1 to 7 of 7

Thread: Convert To Numbers from Text
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2014
    Location
    Houston
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Convert To Numbers from Text

    Hello Message Board: I am trying to convert a small data set from text to number ( AMOUNT Column) . I have tried several methods including text to columns , Fomulas like =VALUE(TRIM(a3)), Trim, etc and I cannot get rid of the trailing spaces to the right. I need a formula solution for this. I know that I can easily resolve this in PQ but I am curious to see why I cannot change the text to numbers. Thanks in advance

    Here is the file in the link below.

    https://www.dropbox.com/s/snjzd4ua8zkiad7/convert%20to%20numbers.xlsx?dl=0

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert To Numbers from Text

    Try

    Code:
    =LEFT(I2,FIND(" ",I2,1))+0
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Sep 2014
    Location
    Houston
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert To Numbers from Text

    Mr Michael! Thank you very muchhhh…. My goodness.. I have tried in all sorts of forms to get the text to number. Quick question: why the
    FIND(" ",I2,1 Piece...? I guess thats the heart of the formula Thanks Again....

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert To Numbers from Text

    Basically, it is saying
    extract all the cells from the LEFT
    Code:
    =LEFT(I2
    Until you FIND a space (" ")
    and by adding a zero on the end it converts the text to a number

    Hope that helps
    Last edited by Michael M; Sep 6th, 2019 at 09:21 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    Board Regular
    Join Date
    Sep 2014
    Location
    Houston
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert To Numbers from Text

    Mr Michael: Very elegant solution. I got it now. Thank you soooo much again... I will practice this several times...Have a great day...!

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert To Numbers from Text

    Glad to help.....and thx for the feedback...
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    Board Regular
    Join Date
    Sep 2014
    Location
    Houston
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert To Numbers from Text

    Mr Michael.. on the contrary... thanks for your help and your quick reply. I was getting so frustared this afternoon since i started doing text to columns and it did not work. Then i tried the trim function and that did not work either..... but now, i learned your trick!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •