Reverse String

[jackgn]

Hi All,

I need a macro to track the reverse string of a word for e.g. in my excel sheet i has a cell with the data as below
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64>command: E\:\Autosys_Jobs\IL\N014.bat</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>E\:\Autosys_Jobs\IL\N009.bat</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>E\:\Autosys_Jobs\IL\N010.bat</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>E\:\Autosys_Jobs\IL\S117.bat</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>E\:\Autosys_Jobs\IL\S109.bat</TD></TR></TBODY></TABLE>
wherein i need to track only the last file name like N014.bat, N009.bat so on and place this value in seperate sheet. can any one help me on this.
Regards
Jack

 

[kpark91]

something like this?

Sub Test()
    Dim strTest, buffer As String
    Dim pos, prevPos As Long
    
    prevPos = 0
    
    strTest = "E\:\Autosys_Jobs\IL\N014.bat"
    
    For n = 1 To Len(strTest)
        pos = InStr(n, strTest, "\")
        If (pos > prevPos) Then
            prevPos = pos
        End If
    Next n
    
    buffer = Right(strTest, Len(strTest) - prevPos) 'RESULT
    
End Sub

 

[hiker95]

jackgn,


What is the worksheet name where the displayed data resides?

What is the first cell in that column that contains?
E\:\Autosys_Jobs\IL\N014.bat


What is the worksheet name, and starting cell, where the N014.bat is to be copied to?

 

[hiker95]

jackgn,

We can get the last part of the path like this:

Excel Workbook
	A	B
1	E\:\Autosys_Jobs\IL\N014.bat	N014.bat
2	E\:\Autosys_Jobs\IL\N009.bat	N009.bat
3	E\:\Autosys_Jobs\IL\N010.bat	N010.bat
4	E\:\Autosys_Jobs\IL\S117.bat	S117.bat
5	E\:\Autosys_Jobs\IL\S109.bat	S109.bat
6
Sheet1

The formula in cell B1 copied down:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",100)),100))

 

[jackgn]

Hey thanks a ton man, it is working fine, now only thing the result needs to be extracted into a new sheet say as sheet2. also i need this in a macro, where it should check the complete column of A.

 

[hiker95]

jackgn,


Sample raw data in worksheet Sheet1, beginning in cell A1:

Excel Workbook
	A
1	E\:\Autosys_Jobs\IL\N014.bat
2	E\:\Autosys_Jobs\IL\N009.bat
3	E\:\Autosys_Jobs\IL\N010.bat
4	E\:\Autosys_Jobs\IL\S117.bat
5	E\:\Autosys_Jobs\IL\S109.bat
6
Sheet1

Before the macro in worksheet Sheet2:

Excel Workbook
	A
1
2
3
4
5
6
Sheet2

After the macro in worksheet Sheet2:

Excel Workbook
	A
1	N014.bat
2	N009.bat
3	N010.bat
4	S117.bat
5	S109.bat
6
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetBat()
' hiker95, 05/2-/2011
' http://www.mrexcel.com/forum/showthread.php?t=551677
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, NR As Long, Sp
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
NR = 0
For Each c In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
  NR = NR + 1
  Sp = Split(c, "\")
  w2.Cells(NR, 1).Value = Sp(UBound(Sp))
Next c
w2.UsedRange.Columns.AutoFit
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the GetBat macro.

 

[kpark91]

Sub Test()
Dim strTest, buffer As String
Dim pos, prevPos As Long, i As Long
 
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
prevPos = 0
pos = 0
strTest = Range("A" & i).Value
For n = 1 To Len(strTest)
pos = InStr(n, strTest, "\")
If (pos > prevPos) Then
prevPos = pos
End If
Next n
Next i
 
Sheet2.Range("A" & i).Value = Right(strTest, Len(strTest) - prevPos) 'RESULT
 
End Sub

 

[p45cal]

kpark91, check out the InStrRev function!

 

[kpark91]

-_- OMG.

Thank you so much p45cal
learnninggg XD

 

[kpark91]

Here is the new code

Sub Test2()
    Dim strTest, buffer As String
    Dim pos, prevPos As Long, i As Long
     
    For i = 1 To Range("A" & Rows.count).End(xlUp).Row
        prevPos = 0
        pos = 0
        strTest = Range("A" & i).Value
        Sheet2.Range("A" & i).Value = Right(strTest, Len(strTest) - InStrRev(strTest, "\")) 'RESULT
    Next i
    
End Sub

Once again, thank you to p4scal :P
worthy name lolll