• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Update based on column contents VBA

I have a excel page with 40k plus records
Using VBA I would like to

I need to look in column H and find where the FIRST  7 characters are a :

4 digit number space - space
Example:  "9021 - "

When that is found place just the 4 digits (General formatting) in column A

ColA          ColH
                 9012 - auhviwbedfvivbfpwebfr
                 9012 - (*&^(^&RUO&*)HJOGUO

gets me this
ColA          ColH
9012           9012 - auhviwbedfvivbfpwebfr
 9016          9016 - (*&^(^&RUO&*)HJOGUO
Larry Brister
Larry Brister
  • 2
  • 2
2 Solutions

Open in new window

I am assuming your data starts at "H2", if not change it to "H1" or whichever cells it starts
Glenn RayExcel VBA DeveloperCommented:
I agree with a non-programmatic solution like Shanan212 suggested.  

I would further refine the function to test specifically for a four-digit value followed by the "space-hyphen-space" string as follows:

In cell A2 (presuming data starts in row 2) and copied on down:

=IF(AND(ISNUMBER(VALUE(LEFT(H2,4))),MID(H2,5,3)=" - "),VALUE(LEFT(H2,4)),"")

Sub sample()

Dim iNum As Long, Count As Long, sa As String
iNum = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Offset(0, 0).Row

Count = 1

Do While Not Count = iNum + 1
    If (Mid(Cells(Count, 8).Value, 6, 1) = "-") Then
        Cells(Count, 1).Value = Left(Cells(Count, 8), 4)
    End If
    Count = Count + 1

End Sub

Open in new window

Larry Bristersr. DeveloperAuthor Commented:
I came up with a non-progrogramatic approach with this...

=RIGHT(LEFT(h3, SEARCH(" - ",H3,1)),5)

So you guys put me on the right track.

Want to split points?
Larry Bristersr. DeveloperAuthor Commented:
Put me on the right track.  Thanks
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

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

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