MrExcel.com - Your One Stop for Excel Tips & Solutions

creating a "," every 2nd character through a string of text???

Posted by Kevin on January 21, 2002 10:56 AM

I have strings of text ranging from 4 characters to 144 characters in a column, and I need to insert a comma after every 2 characters all the way through the string of text until it reaches the end.
The text is this:
EOAACADADZENFFGABJBSB2BABMBWB6BHBRBZBLBVB5BCBPBYBKBUB3BBBNBX
Each 2 characters symbolizes a specific "business segment. Any help is greatly appreciated.

Posted by Juan Pablo G. on January 21, 2002 11:26 AM

Try this UDF

Function InsertComma(Text As String) As String
If Len(Text) < 3 Then InsertComma = Text
Dim i As Integer
Dim T As String
T = Left(Text, 2)
For i = 3 To Len(Text) Step 2
T = T & "," & Mid(Text, i, 2)
Next i
InsertComma = T
End Function

Juan Pablo G.

Posted by Kevin on January 21, 2002 1:11 PM

The code tells me "ambiguous name detected" - help

Is this going into the VB code? I am fairly new to all this, and I am not sure how to even get it to run after pasting into the VB code page. It try's to get me to make a Macro of it, when I try to step into or play it from the VB page, and then it tells me "ambiguous name detected".

Posted by Juan Pablo G. on January 21, 2002 1:26 PM

Re: The code tells me "ambiguous name detected" - help

Ok, do this. While in Excel, press Alt F11. Then, go to the Insert Menu, and select Module. That should create a new, empty module. Paste the entire code I gave you there.

Now, close this. Go back to Excel.

In any cell put your string "EOAACADADZENFFGABJBSB2BABMBWB6BHBRBZBLBVB5BCBPBYBKBUB3BBBNBX"

I'm assuming this is in A1. In another cell put this formula

=InsertComma(A1)

You should see what you want.

Juan Pablo G. Is this going into the VB code? I am fairly new to all this, and I am not sure how to even get it to run after pasting into the VB code page. It try's to get me to make a Macro of it, when I try to step into or play it from the VB page, and then it tells me "ambiguous name detected".

Posted by Kevin on January 21, 2002 2:15 PM

Works Magnificently - Thanks again, you are a true GURU

Now, close this. Go back to Excel. In any cell put your string "EOAACADADZENFFGABJBSB2BABMBWB6BHBRBZBLBVB5BCBPBYBKBUB3BBBNBX" I'm assuming this is in A1. In another cell put this formula =InsertComma(A1) You should see what you want. : Is this going into the VB code? I am fairly new to all this, and I am not sure how to even get it to run after pasting into the VB code page. It try's to get me to make a Macro of it, when I try to step into or play it from the VB page, and then it tells me "ambiguous name detected".