PDA

View Full Version : Manipulate a string



khalid79m
09-02-2009, 08:27 AM
In range a1 to a500 (lastrow is variable , it could be A300 one day and A700 the next) I have text that looks similar to this

"aaaa, aaaa, aaaa, aaedwerr, dfsfdgf, afdgfdg, adgfdgf, adffgffg,"

I need to remove the duplicates so I would be left
with

"aaaa, aaedwerr, dfsfdgf, afdgfdg, adgfdgf, adffgffg,"

the split this out across the cells to the left

so if this was in a1 "aaaa, aaaa, aaaa, aaedwerr, dfsfdgf, afdgfdg, adgfdgf, adffgffg,"

then
b1 = aaaa
c1 = aaedwerr
d1 = afdgfdg
e1 = adgfdgf
f1 = adffgffg

can anyone help ?

mdmackillop
09-02-2009, 08:44 AM
Option Explicit
Sub SplitStuff()
Dim txt, s
Dim i As Long, Col As Long
For Each s In Selection
Col = 1
txt = Split(s, ",")
For i = 0 To UBound(txt)
If Application.CountIf(Range(s.Offset(, 1), s.Offset(, Col + 1)), Trim(txt(i))) = 0 Then
s.Offset(, Col) = Trim(txt(i))
Col = Col + 1
End If
Next
Next
End Sub

khalid79m
09-02-2009, 08:54 AM
How can I remove duplicates in the string?

nst1107
09-02-2009, 08:58 AM
Unless you need this done programmatically... Here is a good article showing how to delete duplicates in Excel 2003 relatively painlessly. http://office.microsoft.com/en-us/excel/HA010346261033.aspx?pid=CL100570551033 Excel 2007 has an even easier one-click button to do it for you. Either way, after that you can use the TRANSPOSE function to split the list out to the right.

khalid79m
09-02-2009, 08:59 AM
no it needs to be done by programming

mdmackillop
09-02-2009, 09:15 AM
Create a new string in the loop and write it in at the end

str = str & Trim(txt(i)) & ", "

khalid79m
09-02-2009, 09:40 AM
confused, im not that good with the programming.. sorry mdmackillop

mdmackillop
09-02-2009, 09:51 AM
Option Explicit
Sub SplitStuff()
Dim txt, s As Range, str As String
Dim i As Long, Col As Long
For Each s In Selection
Col = 1
txt = Split(s, ",")
For i = 0 To UBound(txt)
If Application.CountIf(Range(s.Offset(, 1), s.Offset(, Col + 1)), Trim(txt(i))) = 0 Then
s.Offset(, Col) = Trim(txt(i))
str = str & Trim(txt(i)) & ", "
Col = Col + 1
End If
Next
s.Value = Left(str, Len(str) - 2)
Next
End Sub

khalid79m
09-08-2009, 03:43 AM
This dummy data shows from a3 downwards errors ,

Each cell could have 1 error or more. The errors are split by a ",".
Some cells can be blank some , may have "n/a" or "No_Error".

I need to normalise the data then split it along the cells to the right OF each row.

normalising data:
N/a or No_error are standalone, they cannot have other entries next to them eg

ZA26, No_Error

this has been input incorrectly by the inputter. This should be ZA26 and not ZA26, No Error.

same rule applies for N/A.

Can u help ?

Can

mdmackillop
09-09-2009, 12:40 AM
This doesnt seem to work, i have attached dummy data

Does it not work on the data sample you provided, or on your new sample data?
If the former, where does it break down? If the latter, you seem to asking a totally different question.

I'm not clear what should be ignored/output. Rather than 10,000 rows of meaningless data, how about a dozen rows showing possible combinations and desired outputs, with explanatory notes.