Solved

Adding Spaces to a Number in Excel - Excel Macro

Posted on 2012-04-09
2
437 Views
Last Modified: 2012-04-09
Hi

I have a series of numbers in column E of my spreadsheet that I want to a space after every 2 digits.  

For example, I want to change  21550080140210  in cell E10 to  21 55 00 80 14 02 10 and then continue down the column (E11 to the end) until all numbers have been changed

How do I approach this situation in Excel to create a macro to do this.

Thanks for checking my question out

Mike
0
Comment
Question by:BulldogMike
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37823545
If the numbers are numerical then numberformat the cells as custom format using ## ## ## ## ## ## ## ## ##

If they are text entries then try this macro

Sub splittwos()
    Dim cel As Range, i As Long
    For Each cel In Range("E10:E" & Range("E10").End(xlDown).Row)
        For i = Len(cel) + (Len(cel) + 1) Mod 2 To 1 Step -2
            cel = WorksheetFunction.Replace(cel, i, 0, " ")
        Next i
    Next cel
End Sub
0
 

Author Closing Comment

by:BulldogMike
ID: 37823872
My data was numeric so this was an easy fix. - Thanks

Look for my next question on how to pull descriptions for these codes from Spreadsheet B into spreadsheet A where the codes reside

Thanks

Mike
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Export Outlook 'ghost contacts' 11 38
Formula to pull out part of text in cell 3 25
Stop excel from formatting numbers 4 21
Excel Formula 6 26
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question