+ Reply to Thread
Results 1 to 11 of 11

Need a Macro / VBA to find numbers in a text string and add a thousands comma

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    20

    Need a Macro / VBA to find numbers in a text string and add a thousands comma

    I have had no luck finding a solution to my problem but am confident it can be done... My cell contents are formatted as text and some contain numbers, some do not, some already contain thousands separators, others do not. Here is an example:

    Speed: 20000

    Free Speed: 120000

    Spindle Speed: 15,000

    Voltage: 115V/230V

    Motor HP: 1-1/5

    If the macro were applied to a selection containing the above examples, I would like for it to only update the number values in 'Speed' and 'Free Speed' to 20,000 and 120,000 respectively. Any suggestions? Thanks in advance and happy holidays!

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    attach a sample file
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    Why dont you try this code to see if this can clear your problem? Please give your feedback.
    PHP Code: 
    Sub test()
    Dim tempiresult
    temp 
    Range([A1], [A65536].End(3)).Value
    For 1 To UBound(temp)
       
    With CreateObject("vbscript.regexp")
          .Global = 
    True
          
    .Pattern ".* "
          
    result = .Replace(temp(i1), "")
          If 
    IsNumeric(resultThen
             result 
    Format(result"#,###")
             
    temp(i1) = Left(temp(i1), InStrRev(temp(i1), " ")) & result
          End 
    If
       
    End With
    Next
    [A1].Resize(11) = temp
    End Sub 
    Below is another solution to your problem
    PHP Code: 
    Sub test2()
    Dim Stemp(), tempresulti
    Stemp 
    Range([A1], [A65536].End(3)).Value
    For 1 To UBound(Stemp)
       
    With CreateObject("vbscript.regexp")
          .Global = 
    True
          
    .Pattern "\d+"
          
    Set temp = .Execute(Stemp(i1))
           For 
    Each result In temp
             
    If result 999 Then
                Stemp
    (i1) = Replace(Stemp(i1), resultFormat(result"#,###"))
             
    End If
           
    Next
       End With
    Next
    [A1].Resize(11) = Stemp
    End Sub 
    Last edited by quanghai; 12-27-2012 at 07:18 AM. Reason: Removed Quote of post #1 - Not Needed

  4. #4
    Registered User
    Join Date
    09-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    Hi quanghai, thanks for your post. Unfortunately the macro didn't seem to change any of the values in my worksheet. I have attached a sample file. Thanks in advance!

  5. #5
    Registered User
    Join Date
    11-27-2012
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    Quote Originally Posted by chicolocal View Post
    Hi quanghai, thanks for your post. Unfortunately the macro didn't seem to change any of the values in my worksheet. I have attached a sample file. Thanks in advance!
    Hi. I tested the code and the result is as what you need. Please take a look at the attached file
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    Thanks quanghai!!! I didn't notice until now that the code only made changes to column A and was looking for the change in other columns. I have edited it to work for my selection and it works perfect. Much appreciated!
    Last edited by chicolocal; 12-27-2012 at 02:04 PM.

  7. #7
    Registered User
    Join Date
    09-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    I am having trouble editing the macro to apply to the selected cells on the active sheet. Any advice?

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    Hello chicolocal,

    Which macro are you using Test or Test2?
    Are you manually selecting cells on the worksheet?
    Are the cells all contiguous (touching each other)?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  9. #9
    Registered User
    Join Date
    09-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    I am using the Test2 macro and yes, the cells are contiguous.

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    Hello chicolocal,

    Here is the updated macro...
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Need a Macro / VBA to find numbers in a text string and add a thousands comma

    Works perfect! Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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