Excel VBA - Substring & Paste

Question

I have a column that has U.S. state abbreviations (MI, NY, FL, etc.). I have an issue where there is an observation that is listed as "NJ NJ". This observation moves around within the same column each week.

I want to create a macro that substrings each observation to two characters and just drops everything afterwords.

Would I be able to use the Mid function to grab the first two characters, and then paste it overtop of the original value. Further, would it be appropriate to offset by one or is there a way to do it all at once?

Thanks!

Just use Left(value, 2)

Daniel
– Daniel

2013-12-03 18:27:58 +00:00
Commented Dec 3, 2013 at 18:27 — Daniel
– Daniel, Commented Dec 3, 2013 at 18:27

Shiva · Accepted Answer · 2013-12-03 18:32:07Z

1

Assuming you have your List in the 1st Column, starting at Row 1, the following Macro will do it. Obviously you can make a lot of improvements and error checks to the code, but this should get you started.

Sub FixStates()

Dim lRow As Long
Dim lCol As Long
Dim strContent As String

    lRow = 1
    lCol = 1

    Do While Cells(lRow, lCol) <> ""
        strContent = Trim(Cells(lRow, lCol))
        If Len(strContent) > 2 Then Cells(lRow, lCol) = Left(strContent, 2)
        lRow = lRow + 1
    Loop

End Sub

answered Dec 3, 2013 at 18:32

Shiva

21.1k15 gold badges88 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:52:13Z

1

If you want your result to be offset by one cell from the source cell, then the formula by Daniel Cook works fine.

On the other hand, if you want your result to overwrite the source cell, you would have to copy the cell with the result (using the same formula as above) and paste-special as value on top of the source cell (you can do this for many cells at once), or write a VBA sub.

I personally find it simpler the "copy and paste-special as value" way.

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Dec 3, 2013 at 18:33

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges212 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Substring & Paste

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related