text converting

[anilg0001]

My A range has the values like
LD087P_07_99390.xls
LD088P_12Transcraft_33633.xls
RP0260_03Transcraft_71574.xls

i need in B range as
07_99390 LD087P (VIN# 99390) PO#
12Transcraft_33633 LD088P (VIN# 33633) PO#
03Transcraft_71574 RP0260 (VIN# 71574) PO#

number of column are not constant some time it very 3 to 100

 

[anilg0001]

Thanks Mr.Rick Rothstein u did it greatly.
one more doubt some times error occurs because of error on file name. such a cause how can i avoid the error message.

Thanks again

 

[Rick Rothstein]

Give this code a try...

Sub ProcessVinNumbers()
  Dim X As Long, LastRow As Long, CellVal As String, Parts() As String
  Const StartRow As Long = 1
  Const InputColumn As String = "A"
  Const OutputColumn As String = "B"
  LastRow = Cells(Rows.Count, InputColumn).End(xlUp).Row
  For X = StartRow To LastRow
    Parts = Split(Replace(Cells(X, InputColumn).Value, ".xls", "", , , vbTextCompare), "_")
    If UBound(Parts) >= 2 Then Cells(X, OutputColumn).Value = Parts(1) & "_" & Parts(2) & " " & Parts(0) & " (VIN# " & Parts(2) & ") PO#"
  Next
End Sub

 

[anilg0001]

Thanks u did right things to me
may i ask some doubts again

actually i am using this method for creating a mailing discription.

the mail is like this

Hello ,

We have completed all the files and uploaded to the Outbox folder (Apr09).(Month 3 degit + 09 as the date)



97Utility_91902 - LD030G (Vin# 91902) PO# 41645
97Utility_91901 - LD031G (Vin# 91901) PO# 41646
97Utility_20301 - LD032G (Vin# 20301) PO# 41647
93Utility_29301 - LD033G (Vin# 29301) PO# 41648
90Utility_92201 - LD034G (Vin# 92201) PO# 41649
88Utility_88406 - LD035G (Vin# 88406) PO# 41650
88Utility_12203 - LD036G (Vin# 12203) PO# 41651
84Utility_38201 - LD037G (Vin# 38201) PO# 41652
84Utility_20301 - LD038G (Vin# 20301) PO# 41653
91Dorsey_28599 - LD039G (Vin# 28599) PO# 41654
We have attached the Title Images to appropriate unit.


Look forward to your comments.



Regards,
Anoop.


i want these in my B range of excel
can u?


if u are busy dont try.