+ Reply to Thread
Results 1 to 8 of 8

Special Text Formatting

  1. #1
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Special Text Formatting

    I'm trying to input a product version number into excel in the form of X.X.X

    Based on these versions, I have a conditional formatting rule set up when the next iteration is met. The problem I run into is when I have a version, for example 4.6.9 get updated to 4.6.10

    Because excel reads the number after the second decimal as the most significant, or because 9>1, my conditional formatting is thrown off. Does anyone know a workaround for this, or some special text rule I could use to have excel evaluate this properly?

    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,961

    Re: Special Text Formatting

    make the value 1 til 9 like this => 01 02 03 04 05 06 07 09 09
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Special Text Formatting

    oeldere,

    I forgot to specify that this data is imported from another data source, so manually going through and inserting the zero isn't exactly what I was hoping for. I was thinking of inserting a separate column to create a formula that would make the default text formatted as X.X.XX as you said, I just don't know what that formula would look like.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,961

    Re: Special Text Formatting

    Show the data before and the data you would like as result in a sheet, and i will take a look at it.

    Post your small example of your excel file on the forum.

    Please don't add confidential information in your file.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Special Text Formatting

    If they are always groups of three numbers and only up to 2 digits:
    =TEXT(LEFT(A1,FIND(".",A1)-1)+0,"00.")&TEXT(MID(A1,FIND(".",A1)+1,2)+0,"00.")&TEXT(SUBSTITUTE(RIGHT(A1,2),".","")+0,"00")

    Otherwise, I'd create a function in VBA.
    Remember what the dormouse said
    Feed your head

  6. #6
    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: Special Text Formatting

    Or this ?

    =REPLACE(REPLACE(REPLACE(A1,1,,0),4,,0),7,,0)

    A
    B
    1
    1.1.1 01.01.01
    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

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Special Text Formatting

    Depends if 4.10.1 is a valid entry.

  8. #8
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Special Text Formatting

    I'm not sure if my last response was posted, but I went with Romp's solution. AlKey, Romp was correct, when using your formula, it still treats a version number such as 3.5.10 as 03.05.010

    Thank you everyone for helping out so quickly!

+ 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. Extract text after the LAST special character in a text string
    By phrankndonna in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2013, 11:16 AM
  2. Special Formatting
    By delliott777 in forum Excel General
    Replies: 1
    Last Post: 06-22-2010, 10:43 AM
  3. Special formatting
    By zimtracy in forum Excel General
    Replies: 5
    Last Post: 08-05-2009, 09:44 AM
  4. Special formatting
    By Marcelo Rychter in forum Excel General
    Replies: 2
    Last Post: 04-11-2008, 11:38 PM
  5. Special Formatting
    By montreal1775 in forum Excel General
    Replies: 3
    Last Post: 03-25-2007, 05:45 PM

Tags for this Thread

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