VBA code request to convert number variables

maddiefitz46 · Nov 12, 2014

If wondering if someone would be able to generate a macro to convert numbered data sets:

I have large amounts of data in the rough format x-x-x (where each x represents a different number).

I want to convert this data so that they are formatted to an 18 digit form: xx-xxxx-xxxx-xxxx-xxxx where the number value will remain the same, but the rest of the string is populated with zeroes.

Here are some examples:

4-13-3 should become 04-0013-0003-0000-0000

15-7-12 should become 15-0007-0012-0000-0000

Is this possible?

Warship · Nov 13, 2014

Try:

Code:

Sub CustomNumberFormat()
    Dim numSplit As Variant
    Dim numSrc As String, x As String
    numSrc = "4-13-3"
'    numSrc = "15-7-12"
    numSplit = Split(numSrc, "-")
    x = Format(numSplit(0), "00") & "-" _
      & Format(numSplit(1), "0000") & "-" _
      & Format(numSplit(2), "0000") & "-" _
      & "0000-0000"
    Debug.Print x
End Sub

maddiefitz46 · Nov 14, 2014

Thank you, this is very helpful. I'm having some trouble getting it to run though just because I've never worked with modules in Visual Basic, would someone be able to help me out by outlining the simple steps of going from a new spreadsheet to the finished product?

hiker95 · Nov 14, 2014

maddiefitz46,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Sample raw data (where each cell only contains two - characters):

Excel 2007

A

B

1

4-13-3

2

15-7-12

3

Sheet1

After the macro:

Excel 2007

A

B

1

4-13-3

04-0013-0003-0000-0000

2

15-7-12

15-0007-0012-0000-0000

3

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Sub ConvertTextNumbers()
' hiker95, 11/14/2014, ME817799
Dim c As Range, s
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  s = Split(c, "-")
  c.Offset(, 1) = Format(s(0), "00") & "-" & Format(s(1), "0000") & "-" & Format(s(2), "0000") & "-0000" & "-0000"
Next c
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ConvertTextNumbers macro.

hiker95 · Nov 14, 2014

maddiefitz46,

If each cell can contain less then, or, more than, two - characters, then let us know, and, I will adjust the macro accordingly.

maddiefitz46 · Nov 14, 2014

This is great, thank you so much!!!

Now another sort of challenge going off of this converted data:

1. Sometimes the given number will have a letter in the middle part: 3-2A-6, which I would like to remain in that same converted format to look like 03-002A-0006-0000. Is this possible to do as well?

2. A subset of the data will add a decimal at the end: 4-3-7.5, which should take the last number to a new section to look like 04-0003-0007-0005-0000. Would this be possible?

3. I'm also wondering if code could be written to reverse this process...if given the data set with 03-0004-0006-0000-0000, could you generate the shorter form of 3-4-6?

You guys have no idea how much a working program like this would help me, my department has to process HUGE amounts of data like this and everybody does it manually one by one.

hiker95 · Nov 14, 2014

maddiefitz46,

When you respond to your helper(s), please use their site ID/username/handle.

This will keep thread clutter to a minimum and make the discussion easier to follow.

This is great, thank you so much!!!

Thanks for the feedback.

You are very welcome. Glad we could help.

1. Sometimes the given number will have a letter in the middle part: 3-2A-6, which I would like to remain in that same converted format to look like 03-002A-0006-0000. Is this possible to do as well?

Yes.

2. A subset of the data will add a decimal at the end: 4-3-7.5, which should take the last number to a new section to look like 04-0003-0007-0005-0000. Would this be possible?

Yes.

3. I'm also wondering if code could be written to reverse this process...if given the data set with 03-0004-0006-0000-0000, could you generate the shorter form of 3-4-6?

Yes.

The above is possible based on the present format that contains two - characters in each cell.

But, we would have to see more raw data samples, and, not just one sample of each format, and, what the results should look like?

maddiefitz46 · Nov 14, 2014

GREAT! Below are a bunch of examples for how the results would look:

6-12-2 --> 06-0012-0002-0000-0000
13-2-40 --> 13-0002-0040-0000-0000
1-3B-2 --> 01-003B-0002-0000-0000
10-2-3.2 --> 10-0002-0003-0002-0000
4-6A-5.7 --> 04-006A-0005-0007-0000
12-23-4.2 --> 12-0023-0004-0002-0000
4-9-2.12 --> 04-0009-0002-0012-0000
17-5C-22.4 --> 17-005C-0022-0004-0000
5-3-4 --> 05-0003-0004-0000-0000
1-2-3.4 --> 01-0002-0003-0004-0000
15-12-23.10 --> 15-0012-0023-0010-0000

Is this enough examples?

Rick Rothstein · Nov 14, 2014

hiker95 said:

Rich (BB code):

 Sub ConvertTextNumbers()
' hiker95, 11/14/2014, ME817799
Dim c As Range, s
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  s = Split(c, "-")
  c.Offset(, 1) = Format(s(0), "00") & "-" & Format(s(1), "0000") & "-" & Format(s(2), "0000") & "-0000" & "-0000"
Next c
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub

Just pointing out that you can reduce the number of concatenations in the red highlighted code line by doing it this way...

Code:

c.Offset(, 1) = Format(s(0), "00-") & Format(s(1), "0000-") & Format(s(2), "0000""-0000-0000""")

Rick Rothstein · Nov 14, 2014

maddiefitz46 said:
GREAT! Below are a bunch of examples for how the results would look:

6-12-2 --> 06-0012-0002-0000-0000
13-2-40 --> 13-0002-0040-0000-0000
1-3B-2 --> 01-003B-0002-0000-0000
10-2-3.2 --> 10-0002-0003-0002-0000
4-6A-5.7 --> 04-006A-0005-0007-0000
12-23-4.2 --> 12-0023-0004-0002-0000
4-9-2.12 --> 04-0009-0002-0012-0000
17-5C-22.4 --> 17-005C-0022-0004-0000
5-3-4 --> 05-0003-0004-0000-0000
1-2-3.4 --> 01-0002-0003-0004-0000
15-12-23.10 --> 15-0012-0023-0010-0000

Give this macro a try...

Code:

Sub Reformat()
  Dim X As Long, Cell As Range, Result As String, S() As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    S = Split(Replace(Cell & "-0-0-0-0", ".", "-"), "-")
    Result = ""
    For X = 0 To 4
      Result = Result & "-" & Format(S(X), "@@@@")
    Next
    Debug.Print Mid(Replace(Result, " ", 0), 4)
  Next
End Sub

VBA code request to convert number variables

maddiefitz46

New Member

Excel Facts

Warship

Well-known Member

maddiefitz46

New Member

hiker95

Well-known Member

hiker95

Well-known Member

maddiefitz46

New Member

hiker95

Well-known Member

maddiefitz46

New Member

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

VBA code request to convert number variables

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock