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

 

[Andrew Poulsom]

For the sample data given:

=SUBSTITUTE(MID(A1,8,255),".xls","")&" "&LEFT(A1,6)&" (VIN# "&SUBSTITUTE(RIGHT(A1,9),".xls","")&") PO#"

 

[anilg0001]

Thanks andrew
but i need this as a macro

 

[Andrew Poulsom]

Then record a macro while entering the formula.

 

[anilg0001]

but the number of columns are not constant (Some times it very from 3 upto 100) what i do?

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim LastRow As Long
    Set Sh = Worksheets("Sheet1")
    With Sh
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("B1:B" & LastRow).FormulaR1C1 = "=SUBSTITUTE(MID(RC[-1],8,255),"".xls"","""")&"" ""&LEFT(RC[-1],6)&"" (VIN# ""&SUBSTITUTE(RIGHT(RC[-1],9),"".xls"","""")&"") PO#"""
    End With
End Sub

 

[Rick Rothstein]

but the number of columns are not constant (Some times it very from 3 upto 100) what i do?

Two questions...

What exactly do you mean by "the number of columns are not constant"?

Do you want to change the existing values to your new format in place (that is, within the same cell) or do you want the out in the next column over (like a formula would do)?

Not sure about the first question, but assuming all your values are in Column A and you want to convert them in place, then give this macro a try...

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

I'm going to sleep for the night, so if this macro doesn't do what you want exactly, then maybe you can play around with it... or perhaps you can post what it doesn't do and one of the other voluteers here can maybe fix it for you. If not, I'll be back in a few hours to follow up.

 

[anilg0001]

Thanks rick ur are correct.
i get the answer through your code
but the A1 cell did not get correctly all other A2 to A15 (While testing) are correct.

do u know why a1 get error.
Also i need the answer in B range

 

[Rick Rothstein]

Thanks rick ur are correct.
i get the answer through your code
but the A1 cell did not get correctly all other A2 to A15 (While testing) are correct.

do u know why a1 get error.
Also i need the answer in B range

The reason A1 did not get processed is I started the macro in Row 2. It was late and I was tired so I forgot to mention to you that I had provided a Const (short for constant) statement to control the starting position (which I had defaulted to 2 assuming you had a header row). Anyway, here is my reworked macro, modified to output to Column B (actually, I added two other Const statements to control input and output columns as well, just in case you want to change things later on)...

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), "_")
    Cells(X, OutputColumn).Value = Parts(1) & "_" & Parts(2) & " " & Parts(0) & " (VIN# " & Parts(2) & ") PO#"
  Next
End Sub

 

[RSXchin]

Then record a macro while entering the formula.

That was such a zing! It's good to see someone with so many posts still have such good humor.