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.

 

[Rick Rothstein]

Hi Rick,

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

Can you just copy Column C (that is the column with your values, correct?) with the troublesome values into a new workbook and send me that?

 

[snb_]

Did you overlook http://www.mrexcel.com/forum/excel-questions/746710-change-lenght-character-2.html#post3712179 ??

 

[Venus_excel]

Hi

I cant run this function. It is asking macro name if i try to run.
Guide me how to run.

 

[Venus_excel]

Hi Rick,

I just send sample file contain column C value and expected result in Column D but actual result should be in Column C itself.

Let me know if u need more details.

 

[Rick Rothstein]

Okay, just to keep this thread complete, here is the information the OP sent me...

	C	D	E
1
2	Original Data		Corrected Data
3
4	AB_1234.1		AB_01234.1
5	AB 1234.1		AB_01234.1
6	AB_01234b.1		AB_1234b.1
7	AB_01234		AB_01234
8	AB_1234_111		AB_01234_111
9	AB1234		AB_01234
10	AB1234.1		AB_01234.1
11	AB__1234.2		AB_01234.2
12	AB_012345.1		AB_12345.1
13	AB_1234 XXX 1 (UK70, UK72, UK71)		AB_01234 XXX 1 (UK70, UK72, UK71)
14	AB_12345.1 XXX 2 (UK76)		AB_12345.1 XXX 2 (UK76)
15	AB_12345_XXX4		AB_12345_XXX4
16	AB_01234		AB_01234
17	AB1234.0		AB_01234.0
18	AB12345.1		AB_12345.1
19

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 221px;"><col style="width: 64px;"><col style="width: 228px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The left hand column is the original data and the right hand column is what the OP wants the data to look like afterwards (the corrected data should overwrite the original data in Column C though, it should not be placed in a separate column). This is the macro I came up with to do what the OP wanted (I sent it directly to the OP by email)...

Sub FixUnderscore()
  Dim X As Long, Z As Long, Lpart As String, Rpart As String, vArr As Variant
  vArr = Range("C1", Cells(Rows.Count, "C").End(xlUp))
  For X = 1 To UBound(vArr)
    Do While vArr(X, 1) Like "??[ _][ _]*"
      vArr(X, 1) = Left(vArr(X, 1), 2) & Mid(vArr(X, 1), 4)
    Loop
    If Len(vArr(X, 1)) Then
      Lpart = Left(vArr(X, 1), 2)
      Rpart = Mid(vArr(X, 1), 3 - (vArr(X, 1) Like "??[ _]*")) & " "
      For Z = 1 To Len(Rpart)
        If Not IsNumeric(Mid(Rpart & "X", Z, 1)) Then
          Rpart = Right("00000" & Left(Rpart, Z), 5 - (Mid(Rpart, Z, 1) Like "[. _]")) & Mid(Rpart, Z + 1)
          Exit For
        End If
      Next
    Else
      Lpart = ""
    End If
    If Len(Lpart) Then vArr(X, 1) = Trim(Lpart & "_" & Rpart)
  Next
  Range("C1", Cells(Rows.Count, "C").End(xlUp)) = vArr
End Sub