+ Reply to Thread
Results 1 to 25 of 25

Adding text before number

  1. #1
    Registered User
    Join Date
    11-11-2013
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Adding text before number

    Hi! I have macro which adds text before number like this:

    14,95 -> Norm. 14,95

    It works, but if there's two numbers my code stops working

    14,95-16,95 ..and nothing happens. How can I fix this?

    Please Login or Register  to view this content.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    Not sure I understand what you mean when you say your code stops working and nothing happens.

    If you have 14,95-16,95 in a cell, that's not a number, it's not two numbers, it's a text string made up of numbers, commas and a hyphen.

    Maybe show all the code and, ideally, a sample workbook.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Adding text before number

    Because according to excel 14,95-16,95 is a Text Data

    Do you want to keep 14,95 as real number or it can be converted as text data like Norm. 14,95. Because Adding Norm to a real number within the cell will make the number as text data.

    Please confirm…


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    @Sixthsense: he's just changing the number format. But changing the number format for a cell with text in it, as we've both commented, won't have any effect.


    Regards, TMS

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Adding text before number

    @ TMShucks

    Thanks for reminding me

    Yes me too trying to saying the same as you described in your post.

    At present OP is just adding the text by formatting which will work when the data is Number.

    But as we know it won’t apply the prefix text which is added in formatting if the datas is text.

    So if we add the Prefix Norm. by default to all cells (By way of looping through all cells) then the number value 14,95 will be considered as Text data.

    So that’s why I am asking the OP whether the 14,95 can be converted as text by adding the Prefix WITHIN the cell.

    Quote Originally Posted by :) Sixthsense :) View Post
    Because Adding Norm to a real number within the cell will make the number as text data.

  6. #6
    Registered User
    Join Date
    11-11-2013
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Adding text before number

    Hi and thank you for your replies. I'm sorry I couldn't explain my problem properly, but I attached two worksheets to explain my problem. In example.xlsm you can see how my macro works and in result.xlsm you can see how want it to work. It doesn't matter to me whether the cells end up text data or numbers, I just want the result as shown in result.xlsm

    example.xlsm

    result.xlsm

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Adding text before number

    try
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    One way:

    Please Login or Register  to view this content.

    Regards, TMS

  9. #9
    Registered User
    Join Date
    11-11-2013
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Adding text before number

    Thanks jindon and TMShucks! Those both work!

    As I was testing those with some different numbers, I noticed that if the value of the cell is for example 7,90, it ends up 7,9. Or 0,50 becomes 0,5. Or 25,00 becomes 25. I would like to have two decimals in numbers all the times. Is that possible?

    EDIT:

    Here's example:

    example2_.xlsm

    EDIT2: Working example is example2_.xlsm
    Attached Files Attached Files
    Last edited by Hercules33; 11-12-2013 at 04:36 AM.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    Please Login or Register  to view this content.

    Regards, TMS

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Adding text before number

    This will not convert the cells that are already converted.
    See attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Adding text before number

    Quote Originally Posted by jindon View Post
    This will not convert the cells that are already converted.
    If that is the case then why not simply like this??

    Even the below code wont touch the cells which is previously showing as "Norm. "0.00 when the cell data is real number.

    Slight Change in TMShucks code

    Please Login or Register  to view this content.

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    @Sixthsense: thanks for providing the modification.

    Regards, TMS

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    Thanks for the rep.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved. See post #11.


    Regards, TMS

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Adding text before number

    You guys are totally misreading.

    It will not convert the cells that are properly converted.

    Norm. 14,95
    Norm. 1,95-2,50

    But it will still correct the cells like

    Norm. 6.5 to Norm. 6,50
    Norm. 32.5-40.5 to Norm. 32,50-40,50

    etc.
    Last edited by jindon; 11-12-2013 at 06:54 AM.

  17. #17
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Adding text before number

    Quote Originally Posted by jindon View Post
    You guys are totally misreading.
    I have to say NO in this case!

    Want to know WHY? Read the below quoted content.

    Quote Originally Posted by Hercules33 View Post
    14,95 -> Norm. 14,95

    It works, but if there's two numbers my code stops working

    14,95-16,95 ..and nothing happens. How can I fix this?
    The above words of OP clearly indicates that OP’s system setting has been replaced with comma for decimal places.

    Because the OP might have replaced the dot as comma in his/her control panel settings.

    As per OP’s confirmation the 14,95 is getting changed as Norm. 14,95 with "Norm. ""0.00" formatting, so the comma should be considered as decimal place.

    If you see the post #1 contents then you will realize that the OP’s problem is not about formatting the decimal place of the number. It is all about adding the prefix to the Data. Adding the prefix via formatting to a number will work. But adding prefix to a text data via formatting won’t work as expected.

    So based on all these in depth analysis we just gone for a simple looping system instead of any complicated approach.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Adding text before number

    The OP only knows.

    Thanks for the rep, to OP anyway.
    Quote Originally Posted by :) Sixthsense :)
    instead of any complicated approach.
    Not too complicated for me...
    Last edited by jindon; 11-12-2013 at 07:33 AM.

  19. #19
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Adding text before number

    Why not just use number format:

    Please Login or Register  to view this content.
    So your line of code becomes:

    Please Login or Register  to view this content.
    Last edited by Olly; 11-12-2013 at 07:33 AM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    @OllyXLS: the whole point of this thread is that the OP does not always have numbers in the cells, he has a mixture of digits, commas and hyphens that constitute text. Hence applying a number format to it will not work.

    @jindon: I am always impressed when you and others use Regular Expressions to find and change things. Truly impressed. Sadly, I can look at the code and the regular expressions forever and it simply does not sink in. I think I'm going to have to practise.

    I agree with Sixthsense that, at face value, the simple loop seems to do the trick. That said, I can see how your approach has a benefit in formatting the numbers if there are more than one. Looks as though you're changing the decimal separator though.

    Regards, TMS

  21. #21
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Adding text before number

    Quote Originally Posted by TMShucks View Post
    @OllyXLS: the whole point of this thread is that the OP does not always have numbers in the cells, he has a mixture of digits, commas and hyphens that constitute text. Hence applying a number format to it will not work.

    Did you try my number format, which also formats text in the way he wants?

    It works

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Adding text before number

    @TMShucks

    Can't test my code as comma is not a decimal separator. However I guess it is working somehow as OP attached my code in the latest file.

    Anyway, it might be better to take Text property insteda of Value property, I guess.
    Please Login or Register  to view this content.

  23. #23
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Adding text before number

    @OllyXLS: I stand corrected. You are absolutely right. Please accept my apologies.

    @Jindon: looks like there is a much simpler solution!


    Regards, TMS

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Adding text before number

    Quote Originally Posted by TMShucks View Post
    @Jindon: looks like there is a much simpler solution!
    Partly Yes, but the data like

    1,9-2,5 becomes Norm. 1,9-2,5 not Norm. 1,90-2,50

  25. #25
    Registered User
    Join Date
    11-11-2013
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Adding text before number

    Thank you all, I got it now and I think I learned something too

+ 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. Adding text to number only answer.
    By Zagra147 in forum Excel General
    Replies: 3
    Last Post: 01-21-2011, 05:26 PM
  2. Excel 2007 : Adding text,Result as Number
    By dj0037 in forum Excel General
    Replies: 9
    Last Post: 06-04-2010, 02:22 PM
  3. Adding Text Before A Number
    By icepirates in forum Excel General
    Replies: 6
    Last Post: 06-03-2010, 03:55 AM
  4. Adding a number to text
    By sponge_designs in forum Excel General
    Replies: 3
    Last Post: 12-08-2009, 01:59 PM
  5. adding to a number containing text
    By R D S in forum Excel General
    Replies: 6
    Last Post: 01-21-2005, 12:07 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