Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Adding Spaces to a Number in Excel - Excel Macro

Posted on 2012-04-09
Medium Priority
458 Views
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
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

LVL 43

Accepted Solution

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

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

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month10 days, 2 hours left to enroll