# Thread: CONSISTENT COVERSION OF A CELL (EXCEL)

1. ## CONSISTENT COVERSION OF A CELL (EXCEL)

I Am working on a excel spread sheet which has a bunch of numbers seperated by slashes and dashes. I would like to convert the the values from 1 column of cells to a new consistent format. which looks like this
000-00-00-000-00W0 There is 12 spots for numbers seperated by 4 dashes. 1 dash after three numbers, then 2 numbers and a dash, then two numbers and a dash then three numbers and a dash, then 2 numbers and a captial W and then two numbers. ex 000-00-00-000-00W00 . I have attached a spreadsheet to an example of what I am trying to accomplish. I did some of the conversion manually to show what I am trying to do. Have a look and let me know if there is a way to program a column to convert the values from another column into this format.

2. ## Re: CONSISTENT COVERSION OF A CELL (EXCEL)

You would need some kind of custom function like this:

<pre>Option Explicit
Function ConvertString(vValue)
Dim sTemp As String
Dim iDash As Integer
Dim AWF As WorksheetFunction
Set AWF = Application.WorksheetFunction
With AWF
sTemp = vValue
sTemp = .Substitute(sTemp, "/", "-")
sTemp = .Substitute(sTemp, " ", "")
sTemp = .Substitute(sTemp, "-W", "-00W")
iDash = Len(sTemp) - Len(.Substitute(sTemp, "-", ""))
Select Case iDash
Case 3
sTemp = .Substitute(sTemp, "W", "-00W")
Case 4
Case Else
ConvertString = "Not enough dashes"
Exit Function
End Select
sTemp = .Rept("0", 4 - InStr(sTemp, "-")) & sTemp
sTemp = .Replace(sTemp, 5, 0, .Rept("0", 7 - InStr(5, sTemp, "-")))
sTemp = .Replace(sTemp, 8, 0, .Rept("0", 10 - InStr(8, sTemp, "-")))
sTemp = .Replace(sTemp, 11, 0, .Rept("0", 14 - InStr(11, sTemp, "-")))
End With
ConvertString = Left(sTemp, 18)
Set AWF = Nothing
End Function</pre>

Add it to a module and then use something like:
<pre>=ConvertString(A1)</pre>

There is probably other "validation" that should be done, but this gets all your cases you have here....
Steve

3. ## Re: CONSISTENT COVERSION OF A CELL (EXCEL)

thanks
instead of Vvalue how would i set a loop so it takes A1 value changes it, then A2 ect.... and run through the operation till no more conversions are in column A

4. ## Re: CONSISTENT COVERSION OF A CELL (EXCEL)

Enter =ConvertString(A1) in cell B1, then fill down as far as needed, for example by double clicking the fill handle, i.e. the little black square in the lower right corner of B1.

If you want to use code to change the values "in place" in column A itself:

Sub ConvertValues()
Dim oCell As Range
For Each oCell in Range(Range("A1"), Range("A1").End(xlDown))
oCell.Value = ConvertString(oCell.Value)
Next oCell
Set oCell = Nothing
End Sub

There should be no gaps in the list in column A.

5. ## Re: CONSISTENT COVERSION OF A CELL (EXCEL)

Could I build this into the same module or make a seperate one

6. ## Re: CONSISTENT COVERSION OF A CELL (EXCEL)

Either way you want. Both will work.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•