Change the lenght of the character

[Venus_excel]

Hi all,

Happy to see u again all.
Need your help here. Thanks in Advance.

here i need to change the character length in macro.
example

Column C	Output(same in column c)
AB_1234	AB_01234
AB1234	AB_01234
AB01234	AB_01234
AB_01234a	AB_1234a
AB01234a	AB_1234a
AB_012345	AB_12345
AB 01234	AB_01234

<tbody>
</tbody><colgroup><col><col></colgroup>

Hint :
1) _(underscore) should come at third character
2) Total character of output is 8 character
3) After _(underscore) if it is four character then 0 will come after underscore(_) and if it is five character then 0 wont come.


Please help me to sort out this.

 

[Venus_excel]

Hi Rick,

Compile error : Sub or Function Not defined. - on Substitute word

 

[Rick Rothstein]

Hi Rick,

Compile error : Sub or Function Not defined. - on Substitute word

Sorry, I forgot you wanted a VBA solution instead of a formula one (I gave you a formula). It was not clear to me if this was a separate problem from your original request or not. If it is a separate request (the data you showed originally will not be intermingled with the data you showed in Message #9), then you can use this macro...

Sub FixUnderscore2()
  Dim X As Long, vArr As Variant
  vArr = Range("C1", Cells(Rows.Count, "C").End(xlUp))
  For X = 1 To UBound(vArr)
    If InStr(vArr(X, 1), ".") Then
      vArr(X, 1) = Left(vArr(X, 1), 2) & "_" & Left("00000", 8 - InStr(Replace(vArr(X, _
                   1), "_", "") & ".", ".")) & Mid(Replace(vArr(X, 1), "_", ""), 3)
    End If
  Next
  Range("C1", Cells(Rows.Count, "C").End(xlUp)) = vArr
End Sub

If this is an extension of your first request (the original data will be intermingled with the data you show in Message #9), then you can use this macro for it...

Sub FixUnderscore()
  Dim X As Long, vArr As Variant
  vArr = Range("C1", Cells(Rows.Count, "C").End(xlUp))
  For X = 1 To UBound(vArr)
    If InStr(vArr(X, 1), ".") Then
      vArr(X, 1) = Left(vArr(X, 1), 2) & "_" & Left("00000", 8 - InStr(Replace(vArr(X, _
                   1), "_", "") & ".", ".")) & Mid(Replace(vArr(X, 1), "_", ""), 3)
    ElseIf Len(vArr(X, 1)) > 2 Then
      vArr(X, 1) = Left(vArr(X, 1), InStr(vArr(X, 1) & ".", ".") - 1)
      vArr(X, 1) = Left(vArr(X, 1), InStr(4, vArr(X, 1) & "  ", " ") - 1)
      vArr(X, 1) = Replace(vArr(X, 1), " ", "")
      vArr(X, 1) = Left(vArr(X, 1), 2) & "_" & Right("00000" & Mid(Replace(vArr(X, 1), "_", ""), 3, 9), 5)
    End If
  Next
  Range("C1", Cells(Rows.Count, "C").End(xlUp)) = vArr
End Sub

Just so you know why I was confused, your original request eliminated text after the space whereas your new request keeps text after the space.

 

[Venus_excel]

Hi Rick,

Again getting the below error:

Run-time error '5':
Invalid procedure call or arguments

on the below line
vArr(X, 1) = Left(vArr(X, 1), 2) & "_" & Left("00000", 8 - InStr(Replace(vArr(X, _
1), "_", "") & ".", ".")) & Mid(Replace(vArr(X, 1), "_", ""), 3)

 

[Rick Rothstein]

Hi Rick,

Again getting the below error:

Run-time error '5':
Invalid procedure call or arguments

on the below line
vArr(X, 1) = Left(vArr(X, 1), 2) & "_" & Left("00000", 8 - InStr(Replace(vArr(X, _
1), "_", "") & ".", ".")) & Mid(Replace(vArr(X, 1), "_", ""), 3)

Does the MessageBox with that error give you the opportunity to go into Debug mode? If yes, what value is in vArr(x, 1) when the error occurs?

 

[Venus_excel]

Hi Rick,

The Value is 7

 

[Rick Rothstein]

The Value is 7

In and of itself, that value should not be causing a problem. Can you send me a copy of your workbook so that I can watch the function behave on your actual data rather than my assumption of what is in your cells? Please mention the thread title when you do so I can relate your message back to this thread. My email address is...

rick DOT news AT verizon DOT net

 

[Venus_excel]

Hi Rick,

This are the possiblities.

AB_1234.1

AB 1234.1

AB_01234b.1

AB_01234

AB_1234_111

AB1234

AB1234.1

AB__1234.2

AB_012345.1

AB_1234 XXX 1 (UK70, UK72, UK71)

AB_12345.1 XXX 2 (UK76)

AB_12345_XXX4

AB_01234

AB1234.0

AB12345

<tbody>
</tbody>

I think it is litle bit complex. But help me in this hard situation

 

[Rick Rothstein]

Hi Rick,

This are the possiblities.

AB_1234.1

AB 1234.1

AB_01234b.1

AB_01234

AB_1234_111

AB1234

AB1234.1

AB__1234.2

AB_012345.1

AB_1234 XXX 1 (UK70, UK72, UK71)

AB_12345.1 XXX 2 (UK76)

AB_12345_XXX4

AB_01234

AB1234.0

AB12345

<tbody>
</tbody>

I think it is litle bit complex. But help me in this hard situation

None of those values would ever make V(x,1) in my code be a 7 like you said you got when the error popped up. Are you sure you cannot send the workbook to me so I can watch what is happening as it happens? It would cut a lot of back-and-forth between us as I tried get you to tell me all the things I would be looking for when debugging the code. If you have sensitive information you are worried about, just delete it as my code only processes Column C and doesn't look at anything else.

Also, could you list what you want my code to return to each of those values (it looks like you have mixed data from your two requests)?

 

[snb_]

maybe ?

Function F_snb(c00)
   F_snb = Replace(Left(c00, 2) & "_" & Replace(Replace(Mid(c00, 3), ".", ""), " ", ""), "__", "_")
   If Len(F_snb) > 8 Then F_snb = Left(Replace(F_snb, "_0", "_"), 8)
End Function

 

[Venus_excel]

Hi Rick,

Sorry to say this. i cant share sheet as it is very confidential.

The valus of V(x,1) is AB_01234b.1

let me know if u need more details.