+ Reply to Thread
Results 1 to 7 of 7

Add decimal point to string

  1. #1
    Registered User
    Join Date
    04-14-2014
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Add decimal point to string

    Hi guys

    Great forum and some really interesting reading.

    I can use formula =SUBSTITUTE(A2,(LEFT(A2,3)),(LEFT(A2,3)&"."))
    to add a decimal point in this product code

    start 992709 result 992.709

    Can anybody free/charge help me with
    start 1318241 result 1.318.241


    Thanks
    David

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Add decimal point to string

    try
    =SUBSTITUTE(TEXT(a1,"#,##0"),",",".")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    04-14-2014
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Add decimal point to string

    Works a treat. Many thanks.

    David

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,998

    Re: Add decimal point to string

    Or just change celformat in #.###
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  5. #5
    Registered User
    Join Date
    04-14-2014
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Add decimal point to string

    Hi Willem.

    Thanks for the input. I did try your solution and it works fine for a comma (,) delineated number but a decimal point (.) always shifts to the right.

    David

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Add decimal point to string

    Perhaps

    =REPLACE(A2/1000,2,0,".")

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,998

    Re: Add decimal point to string

    Must be a language problem
    In the Netherlands we use the point (.) as thousand separator

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 06-29-2012, 07:52 PM
  2. Replies: 4
    Last Post: 06-10-2009, 12:50 PM
  3. Format display decimal values but no decimal point
    By BecParko in forum Excel General
    Replies: 1
    Last Post: 10-18-2007, 04:42 AM
  4. Replies: 3
    Last Post: 03-18-2006, 02:25 PM
  5. [SOLVED] FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT.
    By SUKYKITTY in forum Excel General
    Replies: 3
    Last Post: 07-06-2005, 09:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1