VBA code for sorting text

[engine22]

Hi everyone,

Here is my problem:

I have a column of over 1000 cells with context in it. Each cell as a keyword in parathesis associated with the context. Therefore, different context are associated with the same keyword. However, the cells with the same keywords are spread out all over the 1000 cells.

Example:

cell1: anti-lock brake system (abs)
cell2: baromic pressure (Baro)
cell3: anti brake systematic (abs)
...


Basic solution:
I need to create a vba code that would look through the 1000 cells for a specific keyword and copy all the cells having that keyword in parathesis into another spreadsheet or column, so I can look through them easily. so if i look for (abs) it will give me:

cell1: anti-lock brake system (abs)
cell3: anti brake systematic (abs)
...

Ultimate solution:
The VBA code would be "smart" enough to copy all 1000 cells in keywords order. It would give me all possibilities:

cell1: anti-lock brake system (abs)
cell3: anti brake systematic (abs)
cell2: baromic pressure(Baro)
...

I hope i was clear enough and someone can help me... it would save me a ton of time...


Cheers!

-Engine22

 

[ColinKJ]

Hi engine22,

Do you need to keep the format you have.

If the Key Word was leading instead of trailing, you could just sort.

If that's not possible there are other solutions.

ColinKJ

 

[engine22]

Hi,

Thank you for your help...

The thing is that sometimes the keyword (ABS) has a context that doesn't directly corresponds. Here are possible context for the keyword (ABS):

front wheel speed sensor (ABS)
4WAL 4 Sensor (ABS)
Traction Control System Hydraulic 4WAL (ABS)
4WAL 3 Sensor (ABS)

Therefore, I don't thing sorting would work or would it?

-Engine22

 

[engine22]

In simple terms,

I basically want to sort the colums by the keyword found in the paranthesis... The paranthesis are mostly at the end of the cell, but it sometimes appear elsewhere...

Cheers,

engine22

 

[ColinKJ]

Hi engine22,

Are there ever more than one set of paranthesis in one cell,

I was thinking of moving the Key to the left and sorting like:

cell1: (abs) anti-lock brake system
cell3: (abs) anti brake systematic
cell2: (Baro) baromic pressure


ColinKJ

 

[engine22]

Yeah that would work... but would I have to move the keyword manually to the front?

Because i have over a thousands cells so it would take me a long time to move those keywords to the front...

Let me know if you have a way of doing that...

 

[GTO]

Greetings,

In a copy of your workbook (case I goober this up, we don't wreck your work), try:


In a Standard Module:

Sub redo()
Dim rcell As Range
For Each rcell In Selection
    If Not InStr(1, rcell, "(", vbTextCompare) = 0 _
    And Not InStr(1, rcell, ")", vbTextCompare) = 0 Then
 
 
        rcell.Value = _
        Trim(Right(rcell, Len(rcell) - (InStr(1, rcell, "(", vbTextCompare)) + 1) & _
        Chr(32) & _
        Left(rcell, Len(rcell) - (Len(rcell) - InStr(1, rcell, "(", vbTextCompare) + 1)))
    End If
Next
End Sub

Select the cells that you want changed around, press Alt + F8 and run. Remember to remove the macro/module lest you accidently run 'er again.

Hope this helps,


Mark

 

[ColinKJ]

Hi engine22,

If you struggle with Mark's solution, try pasting this in to a standard module
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Code:
Sub RearrangeAndSort()
nr = Sheets(1).Range("A65536").End(xlUp).Row
For a = 2 To nr
x = Cells(a, 1)
b = 1
st:
If Mid$(x, b, 1) <> "(" Then b = b + 1: GoTo st
c = 1
st1:
If Mid$(x, c, 1) <> ")" Then c = c + 1: GoTo st1
d = Mid$(x, b, (c - b) + 1) + Mid$(x, 1, b - 1)
Cells(a, 1) = d
Next a
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Cells(1, 1).Select
End Sub

Code:

This assumes the data is in Column A, and that you have a header in A1.

You will need to enable Macros first.

To run the code, goto Tools>Macro>Macro Select RearrangeAndSort then Click Run

Regards

ColinKJ

 

[mikerickson]

AdvancedFilter will do the filter by keyword and move part.
Use *(abs) in the criteria range.

 

[engine22]

Hi guys,

Thank you for yor solutions, it works great! It will save me a ton of time...

-Engine22