LinkBack URL
About LinkBacks
Hello
I need VB Code to Go thru text in column E
Look for 6 digit check number
If it finds, then put that check number in column D as shown
D8=972201
D9=995955
Please let me know if you have any questions.
Thanks.
R
Hello
I need VB Code to Go thru text in column E
Look for 6 digit check number
If it finds, then put that check number in column D as shown
D8=972201
D9=995955
Please let me know if you have any questions.
Thanks.
R
Hello, try this !
According to your attachment a VBA demonstration as a beginner starter :
PHP Code:Sub Demo1()
Dim Rc As Range, V
For Each Rc In Sheet1.UsedRange.Columns(6 - Sheet1.UsedRange.Column).Cells
V = Right(Rc, 6)
If V Like "######" Then Rc(1, 0) = V
Next
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 11-06-2022 at 01:23 PM. Reason: optimization ...
Hello Marc
Not sure why it is not working
Attached is the pic of the situation, please modifi the code to fix the issue
Thanks
Re: VBB Code to Trim Check number from Text
As my VBA demonstration well works with your attachment !
It's what happens with poor attachment, so weird to not attach a workbook well reflecting the original …
My Excel can't work obviously from a picture so just check the cells contents.
Last edited by Marc L; 11-06-2022 at 02:01 PM.
Hello. Another approach to the problem:
PHP Code:Sub Macro1()
With Range("e2", Cells(Rows.Count, 5).End(xlUp))
.Offset(, -1) = Evaluate("IfError(0 + Right(" & .Address & ", 6), """")")
End With
End Sub
Hello
I am sorry Marc.
Attached is the actual file
Re: VBB Code to Trim Check number from Text
As on my side my VBA demonstration well works with your last attachment for row #139
so check if you have the same post #2 code or erase and copy / paste again …
But according to this last attachment you should obviously better ask for Check # :
PHP Code:Sub Demo2()
Dim V, R&
With Sheet1.Range("D2:D" & Sheet1.[A1].CurrentRegion.Rows.Count)
V = .Columns(2).Value
For R = 1 To UBound(V)
If V(R, 1) Like "Check #*" Then V(R, 1) = Val(Mid(V(R, 1), 7)) Else V(R, 1) = Empty
Next
.Value = V
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 11-06-2022 at 02:39 PM.
Try
in D2
=IF(ISNUMBER(SEARCH("Check",E2)),TRIM(SUBSTITUTE(E2,"Check","")),"")
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Or this ...
According to your last attachment the direct Excel formula way VBA demonstration :
PHP Code:Sub Demo2f()
With Sheet1.Range("D2:D" & Sheet1.[A1].CurrentRegion.Rows.Count)
.Value = .Parent.Evaluate(Replace("IF(LEFT(#,6)=""Check "",RIGHT(#,LEN(#)-6),"""")", "#", .Columns(2).Address))
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Hello Marc
Thanks a lot
R
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks