Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

Cut/remove characters from a Cell after 20 VBA

The formula below was working ok but after some other changes, ....it's doing weird stuff to column A (adding "t" to the beginning and "xt" to the end of whatever is in column A whenever it finds more than 25 characters in a cell in Column H.

'    Sheets("Sheet1").Select
'    Columns("H:H").Select
'    For Each cell In Range("H:H").CurrentRegion  'Edit to desired range
'    cell.Value = Left(cell, 25)
'    Next cell

Is there a better way?

Thanks,

swjtx99
0
swjtx99
Asked:
swjtx99
  • 3
  • 2
  • 2
1 Solution
 
aikimarkCommented:
You should trim the value property of the cells:
cell.Value = Left(cell.Value, 25)

Open in new window

0
 
swjtx99Author Commented:
Hi Aikmark,

Thanks. For some reason it's still changing the cell formats for every other cell on the row. I thought it was just column A but it's messing with every other cell too.

Regards,

swjtx99
0
 
aikimarkCommented:
please post your workbook
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
krishnakrkcCommented:
    Dim r   As Range
    
    With Sheets("Sheet1")
        Set r = Intersect(.UsedRange, .Columns("h:h")) 'adjust Col H to suit
    
        For Each cell In r 'Edit to desired range
            cell.Value = Left(cell, 25)
        Next cell
    End With

Open in new window

0
 
swjtx99Author Commented:
Thanks Krishnakrkc,

Works great although I still don't know why my original code messes with the formats of all cells on the row. Any idea?

Thanks,

swjtx99
0
 
swjtx99Author Commented:
Hi Aikimark,

Sorry I was unable to post the workbook. It has info I can't post. Thanks for trying.

swjtx99
0
 
krishnakrkcCommented:
That's because you were using the currentregion property of the cell.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now