How to split text and merge in new row?

[genetist]

Hi to all
I have data like this

SNP1	SNP2	SNP3	SNP4	SNP5	SNP6
A/A	T/T	G/G	C/C	A/T	G/C
G/G	G/C	G/G	C/C	T/T	MISSING
A/G	NA	G/G	C/C	NA	NA

i have data in 2nd and 3rd rows of above table, i would like to have results like in 4th row. basically (in below table just example of 3 columns (SNP) i would like to split my data separated by / any cell not containing same letters like A/A, T/T, G/G, C/C i would like to give NA to those cells and then no work to do but i want them in final table. my final would be merge of split values like 4th row above table or 6th row of below table

SNP1	SNP2	SNP3
A	T	C
A	T	C
G	NA	C
G	NA	C
A/G	NA	C/C

I tried using concatenate formula =CONCATENATE(LEFT(B2,1),"/",LEFT(B3,1)) but i need to repeat this for right side text too, is there formula to split right and left and concatenate at the same time? any help in this regard is highly appreciated.
Thanks in advance
Regards

 

[RobP]

Hi,

assuming your data is in rows 2 & 3 (starting column A), and is 6 columns across - the following VBA code might help get what you want. Output will start in row 4.
apologies I am not sure how to do in formulas ..

Sub split_rows()

Dim row_num, Col_num As Long
Dim outpuit_val As String

For row_num = 2 To 3
    For Col_num = 1 To 6
        Mytext = ActiveSheet.Range(Cells(row_num, Col_num), Cells(row_num, Col_num)).Value
       
        If Left(Mytext, 1) = Right(Mytext, 1) Then
       
            output_val = Left(Mytext, 1)
        Else
            If UCase(Mytext) = "MISSING" Then
                output_val = "MISSING"
            Else
                output_val = "NA"
            End If
        End If
       
        If row_num = 2 Then
                Range(Cells(row_num + 2, Col_num), Cells(row_num + 2, Col_num)) = output_val
                Range(Cells(row_num + 3, Col_num), Cells(row_num + 3, Col_num)) = output_val
          
        Else 'row_num = 3
       
                Range(Cells(row_num + 3, Col_num), Cells(row_num + 3, Col_num)) = output_val
                Range(Cells(row_num + 4, Col_num), Cells(row_num + 4, Col_num)) = output_val
        End If
             
    Next Col_num
Next row_num

For Col_num = 1 To 6

    digit1 = Range(Cells(5, Col_num), Cells(5, Col_num))
    digit2 = Range(Cells(6, Col_num), Cells(6, Col_num))
   
    If digit1 <> "NA" And digit2 <> "NA" And digit1 <> "MISSING" And digit2 <> "MISSING" Then
   
        output_val = digit1 & "/" & digit2
    Else
        output_val = "NA"
   
    End If

    Range(Cells(8, Col_num), Cells(8, Col_num)) = output_val

Next Col_num

End Sub

 

[genetist]

Dear

Hi,

assuming your data is in rows 2 & 3 (starting column A), and is 6 columns across - the following VBA code might help get what you want. Output will start in row 4.
apologies I am not sure how to do in formulas ..

Sub split_rows()

Dim row_num, Col_num As Long
Dim outpuit_val As String

For row_num = 2 To 3
    For Col_num = 1 To 6
        Mytext = ActiveSheet.Range(Cells(row_num, Col_num), Cells(row_num, Col_num)).Value
      
        If Left(Mytext, 1) = Right(Mytext, 1) Then
      
            output_val = Left(Mytext, 1)
        Else
            If UCase(Mytext) = "MISSING" Then
                output_val = "MISSING"
            Else
                output_val = "NA"
            End If
        End If
      
        If row_num = 2 Then
                Range(Cells(row_num + 2, Col_num), Cells(row_num + 2, Col_num)) = output_val
                Range(Cells(row_num + 3, Col_num), Cells(row_num + 3, Col_num)) = output_val
         
        Else 'row_num = 3
      
                Range(Cells(row_num + 3, Col_num), Cells(row_num + 3, Col_num)) = output_val
                Range(Cells(row_num + 4, Col_num), Cells(row_num + 4, Col_num)) = output_val
        End If
            
    Next Col_num
Next row_num

For Col_num = 1 To 6

    digit1 = Range(Cells(5, Col_num), Cells(5, Col_num))
    digit2 = Range(Cells(6, Col_num), Cells(6, Col_num))
  
    If digit1 <> "NA" And digit2 <> "NA" And digit1 <> "MISSING" And digit2 <> "MISSING" Then
  
        output_val = digit1 & "/" & digit2
    Else
        output_val = "NA"
  
    End If

    Range(Cells(8, Col_num), Cells(8, Col_num)) = output_val

Next Col_num

End Sub

Dear RobP
Good Afternoon

Thanks for your valuable time and efforts to help to solve my problem, will this script will work to any number of columns lets my data is there up to 20 columns (rows always 2&3 only) after changing column number to 20.
Thanking you very much

 

[RobP]

Hi,

yes, as long as you change both lines of code that refer to the col_num (1 to 6 should be 1 - 20 or 1 to whatever you want ).

Glad it worked for you.

cheers
Rob

 

[genetist]

Dear RobP
Good Afternoon
Excellent its working fine after changing column numbers in both the places
Thanks a lot