VBA code request to convert number variables

[maddiefitz46]

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?

 

[shg]

UDF?

Row\Col	A​	B​	C​	D​
1​	6-12-2	06-0012-0002-0000-0000	6-12-2	C1: =Unformat(B1)
2​	13-2-40	13-0002-0040-0000-0000	13-2-40
3​	1-3B-2	01-003B-0002-0000-0000	1-3B-2
4​	10-2-3.2	10-0002-0003-0002-0000	10-2-3-2
5​	4-6A-5.7	04-006A-0005-0007-0000	4-6A-5-7
6​	12-23-4.2	12-0023-0004-0002-0000	12-23-4-2
7​	4-9-2.12	04-0009-0002-0012-0000	4-9-2-12
8​	17-5C-22.4	17-005C-0022-0004-0000	17-5C-22-4
9​	5-3-4	05-0003-0004-0000-0000	5-3-4
10​	1-2-3.4	01-0002-0003-0004-0000	1-2-3-4
11​	15-12-23.10	15-0012-0023-0010-0000	15-12-23-10

Function Unformat(ByVal sInp As String) As String
    sInp = "-" & sInp
    Do While InStr(sInp, "-0")
        sInp = Replace(sInp, "-0", "-")
    Loop
    Unformat = Replace(Trim(Replace(sInp, "-", " ")), " ", "-")
End Function

 

[Rick Rothstein]

Rick - I'm getting a run time error saying "mismatch", do you know how to fix that?

Depends... what line of code did it stop on when it issued that error message and what was in the cell it stopped on?

Do you have your data in Column B like I said I assumed you would?

Note:I tested the code before I posted it and it worked fine for me.

 

[hiker95]

maddiefitz46,

Have you even tried the macro that I provided in my reply #20?