CODEWITHSUNDEEP
excel
morne
morne

Reputation: 4189

Explode number string and change the format

I have many strings such as:

"1.5.22.1.2"

and I would need to pull it apart and put it back together again like this:

"1.05.22.01.02"

The difference is that there are no leading zero for the first one.

This is a big, "copy, manipulate and paste" job because the strings are used as codes in one of out programs.

Upvotes: 0

Views: 106

Answers (2)

3kstc
3kstc

Reputation: 1965

An unorthodox solution is to open a new worksheet and do the following:

1) Copy all the numbers (1.5.22.1.2) you want to change and paste it into the new worksheet.

2) Highlight the cells and go to the DATA tab and select "Text to Column"S

2a) Select Delimited and hit NEXT

2b) Select delimiters as Tab and Other with just a dot:

enter image description here

2c) Hit Finished.

Now each of the numbers should be in a new column, and you are going to use if conditions then concatenate them.

enter image description here

Then you can modify the columns as you wish:

for example I have:

for column 1: =D6

for column 2: =IF(E6<10, CONCATENATE(0,E6),E6)

for column 3: =L6

for column 4: =IF(G6<10, CONCATENATE(0,G6),G6)

for column 5: =IF(H6<10, CONCATENATE(0,H6),H6)

If this is the output you're after then we can squeeze back together by concatenation:

enter image description here

The end product is column P or (my column 6)

The formula for the concatenation is: =CONCATENATE(D6,".",E6,".",F6,".",G6,".",H6)

Upvotes: 1

jkpieterse
jkpieterse

Reputation: 3006

Perhaps a VBA UDF is simpler:

Function FormatThis(oCell As Range) As String
    Dim vValue As Variant
    Dim vSplit As Variant
    Dim lCt As Long
    vSplit = Split(oCell.Value, ".")
    For lCt = LBound(vSplit) + 1 To UBound(vSplit)
        vSplit(lCt) = Format(vSplit(lCt), "00")
    Next
    FormatThis = Join(vSplit, ".")
End Function

Call from your cell as =FormatThis(A2)

Upvotes: 2

Related Questions