VBA to leave only content in string after last “/“ character.

[dugweje]

Hello.

I have an excel file I download that has columns with cell contents that look like this: /great/chicken/sautéed/AA41. I want only the “AA41” left with everything beforehand deleted. The columns are H:1-H:2000. For extra reference there are always 9 “/“ characters so it would always be the only content after the 9th “/“ that is wanted. Thank you.

 

[Saurabhj]

You can use Flash Fill functionality to get the last substring.

1. In Cell I1, type AA41
2. Goto Cell I2.
3. Press Ctrl+E
4. All following cells will filled with required substring.

Book1
	A	B	C	D	E	F	G	H	I	J
1								/great/chicken/sautéed/AA41	AA41
2								/actual/chicken/sautéed/AA4215	AA4215
3								/great/word/sautéed/AA43	AA43
4								/good/mask/sautéed/AA44	AA44
5								/great/chicken/sautéed/AA45	AA45
6								/great/chicken/sautéed/AA46	AA46
7								/great/chicken/sautéed/AA47	AA47
8								/great/chicken/saut/AA48	AA48
9								/great/chicken/sautéed/AA49	AA49
10								/great/chicken/sautéed/AA50	AA50
11								/great/chicken/sautéed/AA51	AA51
12								/great/chicken/sautéed/AA52	AA52
13								/great/chicken/sautéed/BCC	BCC
14								/great/chicken/camel/AA54	AA54
15								/great/chicken/sautéed/AA55	AA55
16								/great/chicken/sautéed/AA56	AA56
17								/great/chicken/sautéed/AA57	AA57
18								/great/chicken/sautéed/AA58	AA58
19								/great/chicken/sautéed/AA59	AA59
20								/great/chicken/sautéed/special	special
21								/great/chicken/sautéed/AA61	AA61
22								/great/chicken/sautéed/AA62	AA62
23								/great/chicken/sautéed/AA63	AA63
24								/great/chicken/sautéed/AA64	AA64
25								/great/chicken/sautéed/AA65	AA65
26								/great/chicken/sautéed/AA66	AA66
27								/great/chicken/sautéed/AA67	AA67
28								/great/chicken/sautéed/AA68	AA68
29
30
31
Sheet1

You can check this to learn Flash Fill.

 

[dugweje]

You can use Flash Fill functionality to get the last substring.

1. In Cell I1, type AA41
2. Goto Cell I2.
3. Press Ctrl+E
4. All following cells will filled with required substring.

Book1
	A	B	C	D	E	F	G	H	I	J
1								/great/chicken/sautéed/AA41	AA41
2								/actual/chicken/sautéed/AA4215	AA4215
3								/great/word/sautéed/AA43	AA43
4								/good/mask/sautéed/AA44	AA44
5								/great/chicken/sautéed/AA45	AA45
6								/great/chicken/sautéed/AA46	AA46
7								/great/chicken/sautéed/AA47	AA47
8								/great/chicken/saut/AA48	AA48
9								/great/chicken/sautéed/AA49	AA49
10								/great/chicken/sautéed/AA50	AA50
11								/great/chicken/sautéed/AA51	AA51
12								/great/chicken/sautéed/AA52	AA52
13								/great/chicken/sautéed/BCC	BCC
14								/great/chicken/camel/AA54	AA54
15								/great/chicken/sautéed/AA55	AA55
16								/great/chicken/sautéed/AA56	AA56
17								/great/chicken/sautéed/AA57	AA57
18								/great/chicken/sautéed/AA58	AA58
19								/great/chicken/sautéed/AA59	AA59
20								/great/chicken/sautéed/special	special
21								/great/chicken/sautéed/AA61	AA61
22								/great/chicken/sautéed/AA62	AA62
23								/great/chicken/sautéed/AA63	AA63
24								/great/chicken/sautéed/AA64	AA64
25								/great/chicken/sautéed/AA65	AA65
26								/great/chicken/sautéed/AA66	AA66
27								/great/chicken/sautéed/AA67	AA67
28								/great/chicken/sautéed/AA68	AA68
29
30
31
Sheet1

You can check this to learn Flash Fill.

Thank you but I prefer a macro/VBA to do this. Plus I don’t want to make another column I only want to edit the H column to include the last string.

 

[bebo021999]

Or:
I1 then drag down:

=TRIM(RIGHT(SUBSTITUTE(H1,"/",REPT(" ",100)),100))

 

[Saurabhj]

Hi,

Check below VBA:

Sub extractData()
Application.ScreenUpdating = False
Dim rowno As Integer
For rowno = 1 To ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
    ActiveSheet.Range("H" & rowno) = Mid(ActiveSheet.Range("H" & rowno), InStr(1, WorksheetFunction.Substitute(ActiveSheet.Range("H" & rowno), "/", "%", 4), "%") + 1, 99)
Next
Application.ScreenUpdating = true
End Sub

 

[Akuini]

Assuming the criteria are met:

The columns are H:1-H:2000. For extra reference there are always 9 “/“ characters so it would always be the only content after the 9th “/“ that is wanted.

try:

Sub dugweje()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("H1:H2000")
    On Error Resume Next
    c = Split(c, "/")(9)
    On Error GoTo 0
Next
Application.ScreenUpdating = True

End Sub

 

[johnnyL]

How about:

Sub TestEndofString()
'
    Application.ScreenUpdating = False
'
    Dim ArrayRow        As Long
    Dim Column_H_Array  As Variant
'
    Column_H_Array = Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)
'
    For ArrayRow = 1 To UBound(Column_H_Array)
        Column_H_Array(ArrayRow, 1) = Right(Column_H_Array(ArrayRow, 1), Len(Column_H_Array(ArrayRow, 1)) - InStrRev(Column_H_Array(ArrayRow, 1), "/"))
    Next
'
    Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row) = Column_H_Array
'
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

I want only the “AA41” left with everything beforehand deleted.

I don’t want to make another column I only want to edit the H column to include the last string.

Try this with a copy of your data.,

Sub LastBit()
  Range("H1:H2000").Replace "*/", "", xlPart
End Sub

 

[dugweje]

This worked perfectly! thank you so much.

Assuming the criteria are met:

try:

Sub dugweje()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("H1:H2000")
    On Error Resume Next
    c = Split(c, "/")(9)
    On Error GoTo 0
Next
Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

This worked perfectly!

I agree, but did wonder if you investigated the option that processing them all at once in a single code line instead of cycling through one at a time?