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
swjtx99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.