Start string at same position in cell

[nyconfidential]

Hi all, I have a sheet that contains two sets of data in the same column. I need the second set to start at the same position each time, for example:

Red 5
Yellow 7
Blue 3

Red/5, etc would be in the same cell. Is there any way I can ensure that the numeric value always starts 10 characters in, no matter what is in the first column(obviously if I just hard coded some spaces in between the color and number the number's starting place would be affected by the number of characters in the color name). I have to do this one column. Thanks!

 

[rlv01]

It seems like you want to replace the existing values in the column, so maybe something like this:

Sub ProcessColumnA()
    Dim rng As Range, R As Range
    Dim SA As Variant

    With ActiveSheet
        Set rng = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With

    For Each R In rng
        SA = Split(Application.Trim(R.Value), " ")
        If UBound(SA) = 1 Then
            R.Value = Left(SA(0) & Space(10), 9) & SA(1)    'Start numeric value at char #10
        End If
    Next R
End Sub

 

[mikerickson]

Two data in one cell is just making work for yourself downstream.
I would recomend that you insert a column, move the numbers to that, with TextToColumns, and then use formatting to hide the grid lines and column Widths to your taste.

 

[rlv01]

Two data in one cell is just making work for yourself downstream.
I would recomend that you insert a column, move the numbers to that, with TextToColumns, and then use formatting to hide the grid lines and column Widths to your taste.

+1. What he said.

 

[nyconfidential]

Thanks all, will heed your advice...