Macro inserting comma & space

**katmison** · 09-30-2009, 03:05 PM

Hello. I am trying to create a macro that will create a comma and space between every 5th character within a cell. I have been unable to get very far at all with that idea, although if anybody can help, that would be preference #1.

What I decided to go with for now, instead, is to try (using the macro recorder) text to columns, fixed width, and do this after every 5th character, and selecting each cell to be formatted as text.

What I am having difficulties with is trying to tell the macro which cells to touch and which ones not to. I do not want the macro to do text to columns and fixed width on simple text. I only want it to do the macro when it is a string of numbers that, during transfer from PDF to excel, have been concatenated into one cell. I also want the macro to do this for infinite number of rows. I have excel 2007.

I am attaching a small excel file with examples of data I am working with. I have saved this excel file as a 97-2003 version for those who do not have 2007.

As I stated, if anybody can help me figure out how to do a simple insert comma space every 5th character where information has been concatenated, that would be preference. I will gladly take any help at all, though.

Also, I am not trying to change anything within column A. Only column B. On the sample file please note B3 - B6. Rows 5 & 6 are fine as they are. Other problem rows include B11 - B13

Thank you!

Please Login or Register  to view this content.

**shg** · 09-30-2009, 03:35 PM

Try this:

Please Login or Register  to view this content.

**katmison** · 09-30-2009, 03:53 PM

Wow! Absolutely incredible! Thank you so much. Works perfectly.

Marking this thread as solved.

**HTIEN60** · 12-21-2011, 10:46 AM

Would someone be able to help me with creating a macro to RIGHT-Align a column, count 16 characters from the right to the left and insert spaces in between the numeric values and alphabetical values. The only problem is that from the left, I would like to have 6 spaces in. If there is 1, 2 or 3 leading alphabetical characters from the left, skip the characters and add only the spaces after the alphabetical characters but before the numberic values. See examples/results below:

Before
C1130119C00004000
AMR120121C00006000
ORCL111217C00018000

After
C 1130119C00004000
AMR 120121C00006000
ORCL 111217C00018000

Any assistance would be greatly appreciate.

**deficit** · 11-29-2012, 10:24 PM

Man I know I am being lazy but have tried my best to modify your macro since I know it has to be simple to do .. I have a column with 10 digits I need to create a macro that puts a comma and a space after the 3rd digit I can get your macro to add the comma and the space but the other 7 digits erase any help would be appreciated.

simple one column of phone numbers
9999991111

needs to be
999, 9991111

Thanks