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

 

[hiker95]

jackgn,

The following macro can be run from either worksheet, and does all the data manipulation within an array.


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).

Option Explicit
Sub GetBatV2()
' hiker95, 05/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=551677
Dim w1 As Worksheet, w2 As Worksheet, S As Variant, a As Long
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
S = w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
For a = LBound(S) To UBound(S)
  S(a, 1) = Trim(Right(Application.Substitute(S(a, 1), "\", Application.Rept(" ", 100)), 100))
Next a
w2.Range("A1").Resize(UBound(S)).Value = S
w2.Activate
End Sub

Then run the GetBatV2 macro.

 

[jackgn]

Hello everone,

Thanks a lot, i got my exact result as expected...

Regards,
Jackgn

 

[jackgn]

Hi sorry to bother you again, I have assing a button for this macro while execution for first time the result is present correct as the sheet is created to sheet2, if i delete the sheet2 and click the button for next time as per the code it through an error "sub script out of range"
can you please help me to short out of this error. Thanks in advance.

Regards,
Jackgn

 

[Andrew Fergus]

The code assumes Sheet2 already exists. Add a new sheet to your workbook, call it Sheet2 and re-run the macro.

Andrew

 

[hiker95]

jackgn,

Per your Private Message request.

The screenshots are the same as my last reply.

If worksheet Sheet2 is not present, or has been deleted, the macro will create it.



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).

Option Explicit
Sub GetBatV3()
' hiker95, 05/23/2011
' http://www.mrexcel.com/forum/showthread.php?t=551677
Dim w1 As Worksheet, w2 As Worksheet, S As Variant, a As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Sheet2!A1)") Then Worksheets.Add(After:=w1).Name = "Sheet2"
Set w2 = Worksheets("Sheet2")
S = w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
w2.UsedRange.Clear
For a = LBound(S) To UBound(S)
  S(a, 1) = Trim(Right(Application.Substitute(S(a, 1), "\", Application.Rept(" ", 100)), 100))
Next a
w2.Range("A1").Resize(UBound(S)).Value = S
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the GetBatV3 macro.

 

[jackgn]

Hi,
An additional task to be added as per the above script only "\" is been tracked if i need to track both "\" and "/" where do i need to insert this line code, can any one help me on this. Thanks in advance

Sub GetBat()
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, NR As Long, Sp
Application.ScreenUpdating = False
Set w1 = Worksheets(InputBox("Enter name of sheet"))
Set w2 = Worksheets(InputBox("Enter name of sheet"))
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

 

[p45cal]

Hi,
An additional task to be added as per the above script only "\" is been tracked if i need to track both "\" and "/" where do i need to insert this line code, can any one help me on this. Thanks in advance

Untested:

Sub GetBat()
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, NR As Long, Sp[COLOR=Red], delim as String[/COLOR]
Application.ScreenUpdating = False
Set w1 = Worksheets(InputBox("Enter name of sheet"))
Set w2 = Worksheets(InputBox("Enter name of sheet"))
NR = 0
For Each c In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
NR = NR + 1
[COLOR=Red]delim = IIf(InStrRev(c.Value, "\") < InStrRev(c.Value, "/"), "/", "\")[/COLOR]
Sp = Split(c, [COLOR=Red]delim[/COLOR])
w2.Cells(NR, 1).Value = Sp(UBound(Sp))
Next c
w2.UsedRange.Columns.AutoFit
w2.Activate
Application.ScreenUpdating = True
End Sub

 

[p45cal]

or (but may come a cropper as untested! (sp is likely not always be an array?)):

Sub GetBat()
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, NR As Long, Sp
Application.ScreenUpdating = False
Set w1 = Worksheets(InputBox("Enter name of sheet"))
Set w2 = Worksheets(InputBox("Enter name of sheet"))
NR = 0
For Each c In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
NR = NR + 1
Sp = Split(c, "\")
[COLOR=Red]Sp= Sp(UBound(Sp))
Sp = Split(c, "/")[/COLOR]
w2.Cells(NR, 1).Value = Sp(UBound(Sp))
Next c
w2.UsedRange.Columns.AutoFit
w2.Activate
Application.ScreenUpdating = True
End Sub

 

[jackgn]

Hi, thank you very much for your help..