+ Reply to Thread
Results 1 to 9 of 9

Convert Text to numbers using function ?

  1. 04-13-2015, 12:45 PM #1
    hanialbarni
    hanialbarni is offline
    Registered User
    Join Date
    03-10-2015
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    11

    Question Convert Text to numbers using function ?

    Hi
    Good morning
    Can someone teach me how can I convert the following text to numbers and add "." for example check the uploaded picture.
    6000 = 6.000
    I know I can easily change it from text to number but I want to use formula.
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 04-13-2015, 12:52 PM #2
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Convert Text to numbers using function ?

    Perhaps

    =A1/1000
    Reply With QuoteRegister To Reply
  3. 04-13-2015, 12:53 PM #3
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Convert Text to numbers using function ?

    Can you attach an actual file.
    Learn Data Analysis or Excel youtube.com/jiejenn
    Reply With QuoteRegister To Reply
  4. 04-13-2015, 12:55 PM #4
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Convert Text to numbers using function ?

    Nm. Jonmo1's suggestion works.
    Reply With QuoteRegister To Reply
  5. 04-13-2015, 12:56 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Convert Text to numbers using function ?

    Try this

    =IF(LEN(A1)<=3,VALUE(A1),A1/1000)

    Row\Col
    A
    B
    1
    		 100
    100
    2
    		 400
    400
    3
    		 300
    300
    4
    		 6000
    6
    5
    		 3210
    3.21
    6
    		 8600
    8.6
    7
    		 10110
    10.11
    8
    		 663000
    663
    9
    		 394613
    394.613
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  6. 04-13-2015, 01:15 PM #6
    Nisha Dhawan
    Nisha Dhawan is offline
    Valued Forum Contributor
    Join Date
    05-07-2014
    Location
    India
    MS-Off Ver
    MS Office 365
    Posts
    313

    Re: Convert Text to numbers using function ?

    Hi,

    Try this one =VALUE(B1)/1000
    Thanks
    Nisha Dhawan


    If you like my answer please click on * Add Reputation
    "If you can dream it, You can do it "
    Reply With QuoteRegister To Reply
  7. 04-13-2015, 01:42 PM #7
    hanialbarni
    hanialbarni is offline
    Registered User
    Join Date
    03-10-2015
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    11

    Re: Convert Text to numbers using function ?

    Thanks man, it worked!

    Can you please explain to me how did it work ? what does len do ? and value ?
    Reply With QuoteRegister To Reply
  8. 04-13-2015, 01:43 PM #8
    hanialbarni
    hanialbarni is offline
    Registered User
    Join Date
    03-10-2015
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    11

    Re: Convert Text to numbers using function ?

    Alkey Thanks man, it worked!

    Can you please explain to me how did it work ? what does len do ? and value ?
    Reply With QuoteRegister To Reply
  9. 04-13-2015, 07:58 PM #9
    tgsekhar
    tgsekhar is offline
    Registered User
    Join Date
    03-02-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Convert Text to numbers using function ?

    Function LEN is used to find the length of the text in a cell. VALUE is used convert text into a number. IF condition is used for conditional division required only when the number is having more than 3 digits.
    If I have helped, click on the * below the post
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Convert numbers stored as text to numbers for range of data that changes
    By excel2425 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2014, 01:57 PM
  2. [SOLVED] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 AM
  3. [SOLVED] How do you apply the int() in VBA in order to convert numbers stored as text to numbers
    By djaurit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2012, 02:57 PM
  4. Convert numbers stored as text to numbers Excel 2000
    By Darlene in forum Excel General
    Replies: 6
    Last Post: 01-31-2006, 04:10 PM
  5. [SOLVED] function to convert whole numbers to written text
    By Mani K in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-29-2005, 06:10 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1