?
Solved

Adding Spaces to a Number in Excel - Excel Macro

Posted on 2012-04-09
2
Medium Priority
?
448 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 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