Combining 2 cell vaules -- without losing data

[Kabeer456]

Good afternoon all-

I have a data set that I'm trying to sort in order to be efficient for some of my team members.


Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive. Any insight will be helpful.
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Col A
[/TD]
[/TR]
[TR]
[TD]123-45678-A-1
[/TD]
[/TR]
[TR]
[TD]123-45678-A-10
[/TD]
[/TR]
[TR]
[TD]123-45678-B-2
[/TD]
[/TR]
</TBODY>[/TABLE]

I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.

My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[/TR]
[TR]
[TD]123-45678-
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]123-45678-
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]123-45678-
[/TD]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]


Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:

Columns("B:B").Select
Selection.NumberFormat = "00"

To produce:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[/TR]
[TR]
[TD]123-45678-
[/TD]
[TD]01
[/TD]
[/TR]
[TR]
[TD]123-45678-
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]123-45678-
[/TD]
[TD]02
[/TD]
[/TR]
</TBODY>[/TABLE]

Now all I need to do is rejoin these 2 columns before sorting. My current code is:

Sub Rejoin_Container_Number()
x = 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Do While Cells(x, 2).Value <> ""
Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value

x = x + 1
Loop
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
End Sub

As many have probably already guessed, this produces the following result:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Col A
[/TD]
[/TR]
[TR]
[TD]123-45678-1
[/TD]
[/TR]
[TR]
[TD]123-45678-10
[/TD]
[/TR]
[TR]
[TD]123-45678-2
[/TD]
[/TR]
</TBODY>[/TABLE]

I'm looking for:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Col A
[/TD]
[/TR]
[TR]
[TD]123-45678-01
[/TD]
[/TR]
[TR]
[TD]123-45678-10
[/TD]
[/TR]
[TR]
[TD]123-45678-02
[/TD]
[/TR]
</TBODY>[/TABLE]
**zeroes in BOLD for reference only, result does not need to be bold**

Anyone have any ideas?

Thanks, as always!

-Brian

 

[timfritsch]

Replace

Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value

with

Cells(x, 1).Value = Cells(x, 2).Value & iif(len(Cells(x, 3))=1,"0" & cells(x,3),cells(x,3))

Tim

 

[CharlesChuckieCharles]

this takes care of it in the cell no spliting it out into columns

Sub Rejoin_Container_Number()
Dim mycell As Range
Dim lastDash As Long
Dim endNum As String
x = 1
Range("A1:A" & Cells(Rows.Count, Columns("A").Column).End(xlUp).Row).Select   'find depth of list

 For Each mycell In Selection
    If mycell.Value <> "" Then
      lastDash = InStrRev(mycell.Value, "-")  'get pos of last dash
      endNum = Format(Mid(mycell.Value, lastDash + 1, 5), "00")
      lastDash = InStrRev(Left(mycell.Value, lastDash - 1), "-")
      mycell.Value = Left(mycell.Value, lastDash) & endNum
    End If
  Next mycell
End Sub

 

[Kabeer456]

Genius!

You can't put a price on another brain and set of eyes. You managed to save me a lot of trouble with minimal code. Thank you!

Cheers-
Brian

 

[Kabeer456]

Sorry if I wasn't clear, but I meant to send kudos to both of you who replied so quickly.

Cheers-
Brian