Change the lenght of the character

Venus_excel

Board Regular
Joined
Dec 18, 2013
Messages
74
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 COutput(same in column c)
AB_1234AB_01234
AB1234AB_01234
AB01234AB_01234
AB_01234aAB_1234a
AB01234aAB_1234a
AB_012345AB_12345
AB 01234AB_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.
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Give this macro a try...

Code:
Sub FixUnderscoreLocation()
  Dim Addr As String
  Addr = "C1:C" & Cells(Rows.Count, "C").End(xlUp).Row
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),LEFT(" & Addr & ",2)&""_""&RIGHT(""" & _
                         "00000""&" & "MID(SUBSTITUTE(" & Addr & ",""_"",),3,9),5),"""")")
End Sub
 
Upvote 0
Thanks Rick..Really its working fine..
But i need one more update.

Column COutput (same in Column C)
NANA
AB_1234 kavinAB_01234
AB_01234 kavinAB_01234
AB_1234.1AB_01234
AB_01234.1AB_01234

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


Help me in this.
 
Last edited:
Upvote 0
Thanks Rick..Really its working fine..
But i need one more update.

Column COutput (same in Column C)
NANA
AB_1234 kavinAB_01234
AB_01234 kavinAB_01234
AB_1234.1AB_01234
AB_01234.1AB_01234

<tbody>
</tbody>


Help me in this.

Your examples do not make it clear... are spaces and dots the only characters which delineate what should not be kept or could there be other characters (such as semi-colons, slashes, dashes, etc.)? Also, are there any other "updates" that you will need (it is so much easier to write code when we know all of the conditions affecting the outcome than to learn them one at a time and then have to keep modifying or throwing out and starting over again in order to account for new information)?
 
Upvote 0
Rick ,

Thanks for your response.
And you are right , spaces and dots the only characters which delineate what should not be kept.
And this the final one and no update required from my side.

please find my sample


Column COutput(same in column c)
AB_1234AB_01234
AB1234AB_01234
AB01234AB_01234
AB_01234aAB_1234a
AB01234aAB_1234a
AB_012345AB_12345
AB 01234AB_01234
NANA
AB_1234 kavinAB_01234
AB_01234 kavinAB_01234
AB_1234.1AB_01234
AB_01234.1AB_01234

<tbody>
</tbody><colgroup><col><col></colgroup>
 
Last edited:
Upvote 0
See if this code does what you want...

Code:
Sub FixUnderscoreLocation()
  Dim X As Long, vArr As Variant
  vArr = Range("C1", Cells(Rows.Count, "C").End(xlUp))
  For X = 1 To UBound(vArr)
    If 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
 
Upvote 0
Hi Rick,
U can sort out this issue in the same manner

Column COutput(same in column c)
AB_1234.1AB_01234.1
AB1234.1 xxxAB_01234.1 xxx
AB__01234.1AB_01234.1
AB__1234a.111AB_1234a.111

<tbody>
</tbody><colgroup><col><col></colgroup>
 
Upvote 0
Hi Rick,
U can sort out this issue in the same manner

Column COutput(same in column c)
AB_1234.1AB_01234.1
AB1234.1 xxxAB_01234.1 xxx
AB__01234.1AB_01234.1
AB__1234a.111AB_1234a.111

<tbody>
</tbody>
Give this formula a try...

=LEFT(A1,2)&"_"&LEFT("00000",8-FIND(".",SUBSTITUTE(A1,"_","")))&MID(SUBSTITUTE(A1,"_",""),3,99)
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top