Results 1 to 4 of 4

Thread: adding comma to string value in Range

  1. #1
    Board Regular
    Join Date
    May 2005
    Posts
    686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default adding comma to string value in Range

    i have 6000 rows in my range, i want to add a comma to the 7 character location from end.

    i have this formula that i tested in a single cell and it works BUT when I try to apply it to the entire range using a With Range, the syntax is off (get mismatch error) can anyone assist with proper syntax

    the formula is. Take the left most characters - last 6, then add a comma, then add back the last 6 characters.

    Code:
    set Rng = range("a1:a6000"
    
    With Rng
            .Value = Left(.Value, Len(Rng) - 6) & "," & Right(Rng, 6)
    End With

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,210
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: adding comma to string value in Range

    You cannot change multiple values like that, try
    Code:
    Sub ilcaa()
       With Range("A1", Range("A" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace("if(len(@)<6, @,replace(@,len(@)-5,0,"",""))", "@", .Address))
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    May 2005
    Posts
    686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: adding comma to string value in Range

    Quote Originally Posted by Fluff View Post
    You cannot change multiple values like that, try
    Code:
    Sub ilcaa()
       With Range("A1", Range("A" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace("if(len(@)<6, @,replace(@,len(@)-5,0,"",""))", "@", .Address))
       End With
    End Sub
    hi fluff

    i need to change the hard coded value of the 6th space from end to a variable.

    i am struggling to write the proper syntax with all those quotes, etc. info on EVALUATE is very limited with only basic examples on www.

    how can i replace the hard-coded len(@)-5 with a variable name?

    thanks alot. hope i made sense

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,210
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: adding comma to string value in Range

    How about
    Code:
    Sub ilcaa()
       Dim Lngth As Long
       Lngth = 6
       With Range("A1", Range("A" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace("if(len(@)< " & Lngth & ", @,replace(@,len(@)-" & Lngth - 1 & ",0,"",""))", "@", .Address))
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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