Sort text in Cell

alexoigres · Jun 12, 2006

Hello,

I want to sort some values inside a cell

Example:

A2
19,48,35,21

What I want is

A2
19,21,35,48

I have a new file every week and I want to create a macro to do this.

Any ideas??
thank you

alexoigres · Jun 20, 2006

I'm sos sorry man It did not work.... : (

I tought it was going to be really easy.... I'm giving up with this..... I can't figure it out..... I can send you my excel file with some data and you can see and example.

Please try to help me with this ok : )

Thank you so much

Zack Barresse · Jun 20, 2006

Maybe you can shorten some of that code a little bit, starting with routines shortened as such...


Private Sub insertFormattedData(wks As Worksheet, lngCount As Long)
 'Insert formatted data back to Excel file
 wks.Range("C" & lngCount).Value = bookCode
 wks.Range("T" & lngCount).Value = series
 wks.Range("U" & lngCount).Value = body
 wks.Range("AB" & lngCount).Value = cntr
 wks.Range("AL" & lngCount).Value = div
 wks.Range("AM" & lngCount).Value = defer
 wks.Range("AN" & lngCount).Value = alliance
 wks.Range("AK" & lngCount).Value = gmrpo
 wks.Range("I" & lngCount).Value = group
 wks.Range("V" & lngCount).Value = qty
 wks.Range("AO" & lngCount).Value = spl
End Sub


Then you can just call like this ...

Dim ws As Worksheet

Public Sub main()
 Dim i As Long 'MODIFY THIS - FIRST ROW TO BE PROCESSED
 Dim j As Long 'MODIFY THIS - LAST ROW TO BE PROCESSED
 i = 4
 j = 7000
 count = i
 Set ws = Worksheets("Original")
 ws.Cells.NumberFormat = "@"
 Call setServiceYear(ws)
 ws.Columns("W").Replace What:="H", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
 ws.Columns("I").Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
 ws.Columns("T").Copy Destination:=ws.Range("AJ1")
 cell = "m" & count
 partnum = ws.Range(cell).Value
 Do While Trim(partnum) <> "" Or count = j + 1
 Call getGMDATdata(ws, count)
 Call formatGroup 'Format group (no .)
 Call formatQty 'Format quantity to 2 digits
 Call convertBookSeriesBody 'Format book code, series, and body style
 Call insertFormattedData(ws, count) 'Insert formatted data back into Excel
 ws.Rows(count & ":" & count).Interior.ColorIndex = 2
 count = count + 1
 cell = "m" & count
 partnum = ws.Range(cell).Value
 Loop
 MsgBox "Format Complete", vbInformation
End Sub

That would give you a couple of different procedures ...


Private Function getGMDATdata(wks As Worksheet, lngCount As Long)
 ctlgType = Trim(wks.Range("D" & lngCount).Value) 'Catalogue Type
 bookCode = Trim(wks.Range("C" & lngCount).Value) 'Book Code
 series = Trim(wks.Range("T" & lngCount).Value) 'Series
 body = Trim(wks.Range("U" & lngCount).Value) 'Body Style
 gmrpo = Trim(wks.Range("AF" & lngCount).Value) 'GM RPO
 drive = Trim(wks.Range("AE" & lngCount).Value) 'Left or Right Drive
 group = Trim(wks.Range("I" & lngCount).Value) 'Group
 qty = Trim(wks.Range("V" & lngCount).Value) 'Quantity
 cntr = ""
 defer = ""
 div = ""
 alliance = ""
 spl = ""
End Function


.. and ..

Private Sub setServiceYear(wks As Worksheet)
 With wks.Columns("P:Q")
 .Replace What:="98", Replacement:="2004", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="01", Replacement:="2004", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="02", Replacement:="2004", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="03", Replacement:="2004", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="04", Replacement:="2004", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="05", Replacement:="2005", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="06", Replacement:="2006", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="07", Replacement:="2007", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="08", Replacement:="2008", LookAt:=xlWhole, SearchOrder:=xlByRows
 .Replace What:="09", Replacement:="2009", LookAt:=xlWhole, SearchOrder:=xlByRows
 End With
End Sub

And when you call your procedures, just change them as such ...

 Call getnorUSChevyRTConversionOptions
 Call getRTDivCntrSeriesBody("1", "UCM")
 Call insertrowtest(6, count)
 Call insertFormattedData(ws, count)

Just be sure to Dim the ws variable in each sub; no need to Dim it if it's in the header of the module declared for the entire module.

Also, no need for the paren's, such as ...

 If ((InStr(series, "ULS") <> 0) And (InStr(series, "ULT") <> 0) And (InStr(series, "US") <> 0)) Then

.. can become ..

 If InStr(series, "ULS") <> 0 And InStr(series, "ULT") <> 0 And InStr(series, "US") <> 0 Then

Zack Barresse · Jun 20, 2006

Also, here is an example of how to use the function with a specified string...

Function mySortCell(body As String, Optional Delimiter As String) As String
 Dim arrTmp() As String, i As Long, j As Long, vElm As Variant
 If Delimiter = "" Then Delimiter = ", "
 arrTmp = Split(body, Delimiter)
 For i = LBound(arrTmp) To UBound(arrTmp)
 For j = i + 1 To UBound(arrTmp)
 If Val(arrTmp(i)) > Val(arrTmp(j)) Then
 vElm = arrTmp(j)
 arrTmp(j) = arrTmp(i)
 arrTmp(i) = vElm
 End If
 Next
 Next
 mySortCell = Join(arrTmp, Delimiter)
End Function

Sub TestThisPlease()
 MsgBox mySortCell("3, 5, 4, 2, 1")
End Sub

You can use the principle of passing variables between subs the same way with functions.

HTH

alexoigres · Jun 22, 2006

Sorry guys.....is not working..... I think I'm going to give up with this.....But first I just want to try something different for last time, but I need your help.

I was thinking to used the fisrt code that firefytr posted here. My idea is add a new code in my macro to create a new column next to column "U" column "V" and used =sortcell(U4,",") all the way down. After that, delete the original column "U" and keep the sort data in column "V". If we delete column "U" column "V" will be Column "U", and with this I don't have to doit manualy.

Can I do something like that? and can you help me to create the macro?
Thank you guys...I think this is going to be the last try.
SG

Zack Barresse · Jun 23, 2006

Why not manually?

Enter the formula in the top-most row, copy down. Select all formula cells in that column, copy it, then Edit | Paste Special | Values, then delete the other column.

alexoigres · Jun 28, 2006

OK THANK YOU SO MUCH..

I THINK I WILL DO IT MANUALLY

I HAVE ANOTHER QUESTION FOR YOU GUYS AND I KNOW YOU CAN HELP ME : ) ... BUT I WILL POST IT LATER...

THANK YOU GUYS FOR EVERYTHING
SG

Sort text in Cell

alexoigres

Board Regular

alexoigres

Board Regular

Excel Facts

Zack Barresse

MrExcel MVP

Zack Barresse

MrExcel MVP

alexoigres

Board Regular

Zack Barresse

MrExcel MVP

alexoigres

Board Regular

Similar threads

Forum statistics

Share this page

Sort text in Cell

alexoigres

Board Regular

alexoigres

Board Regular

Excel Facts

Zack Barresse

MrExcel MVP

Zack Barresse

MrExcel MVP

alexoigres

Board Regular

Zack Barresse

MrExcel MVP

alexoigres

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock