Extracting and Manipulating Text from Cells.

[Living]

Hi,

This could be a bit complicated, but hopefully also useful for others. We have the following sample of 10 cells from a large file with similar data:

N: 1; B: 162; M: 278, 278; T: 24; A: 103, 105; I: 393; Ḥ: 7240, 7390, 7465, 7546, 7617, 7756, 27399, 8380, 8741, 8894, 8985, 9559, 9671, 9741, 10119, 10211; W: 40; D: 766.
N: 2; B: 246, 889, 1136; M: 255, 255; A: 55; I: 286; Ḥ: 22731, 22802, 22857, 22906, 22948; D: 685.
N: 3; B: 244; M: 254; A: 49; Ḥ: 19238.
N: 4; B: 2261, 6923; M: 1733, 1824; A: 2930, 3579, 4354; Ḥ: 19167.
N: 5; Ḥ: 23683, 23811, 24404, 24609, 25483; D: 684.
N: 6; B: 888; Ḥ: 12050; D: 681.
N: 7; B: 887, 7240; M: 252; T: 22; A: 46; I: 287; Ḥ: 7294, 7364, 7457, 7794, 8928, 8941, 9264, 9308, 9612, 10240, 10318, 10487; W: 147, 148; D: 683, 1484.
N: 8; M: 253, 253; A: 51; I: 290; Ḥ: 27601, 24274, 24958, 25020, 25064, 25466.
N: 9; B: 5889, 5891, 6297; M: 257; T: 2756; A: 4198; I: 292; Ḥ: 7092, 7220, 7754, 9066, 9965; W: 1709.
N: 10; B: 5889, 5891, 6297; M: 257; T: 2756; A: 4198; I: 292; Ḥ: 7092, 7220, 7754, 9066, 9965; W: 1709.

We want to extract the same letters with its numbers and put the same letters with numbers in one column and do this for all letters, but - this is important - while keeping them in their original row. The purpose of this is addition of data and easy rearrangement of the letter plus numbers order, e.g., to: B M N A T I W D Ḥ. As you can see not all the rows have all letters, nor are the seizes the same. There are thousands of such cells. Some parts are partly in bold, we want to maintain that layout after sorting. Such a thing should be easy for computers, but how could this extraction and manipulation be done (I have Excel 2010)?

P.S. There are two spaces after every ; that were eaten up here.

 

[Living]

Found this (afterwards) solution for my last question (with dummy/comparison column left):

Sub Hilite()

    Dim rngTextOrig As Range
    Dim rngTextMatch As Range
    Dim lngIndex As Long
    Dim vntWord As Variant
    Dim lngPos As Long
    
    Set rngTextOrig = Range("A1:A10")
    Set rngTextMatch = rngTextOrig.Offset(0, 1)
    
    For lngIndex = 1 To rngTextOrig.Cells.Count
        For Each vntWord In Split(rngTextOrig.Cells(lngIndex).Value, " ")
            lngPos = InStr(1, rngTextMatch.Cells(lngIndex).Value, vntWord, vbTextCompare)
            If lngPos > 0 Then
                With rngTextMatch.Cells(lngIndex).Characters(lngPos, Len(vntWord))
                    .Font.Bold = True
                End With
            End If
        Next
    Next
            
End Sub

 

[Living]

P.S. It seems that the first bold layout can also be restored by performing such a method repeatedly (bolding, then unbolding)!