Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change a value on a string of data
Hi my 1st post so I hope I make sense:
I have strings of data like the EG below 000000000000 0000 000 Andrea xxxx 222 3333 000000000000 0000 000 Louise xxxx 222 3333 So what I need to do is change the names to ***** the names can be different but the field is always the same number of characters and in the same location in the line The file is a TXT file. Any idea? Thank you Andrea |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change a value on a string of data
Sub AABB()
For Each Cell In Range("A1:A10") s = Cell.Value iloc = InStr(23, s, " ", vbTextCompare) s1 = Left(s, 22) s3 = Right(s, Len(s) - iloc) s2 = "***** " Cell.Value = s1 & s2 & s3 Next End Sub -- Regards, Tom Ogilvy "adiacc" wrote: Hi my 1st post so I hope I make sense: I have strings of data like the EG below 000000000000 0000 000 Andrea xxxx 222 3333 000000000000 0000 000 Louise xxxx 222 3333 So what I need to do is change the names to ***** the names can be different but the field is always the same number of characters and in the same location in the line The file is a TXT file. Any idea? Thank you Andrea |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change a value on a string of data
For Tom's code to work you're going to need to read the text file into
a worksheet (each line into a new cell), then run Tom's code, then write the cells back into the text file.. The code for that should be pretty easy to find on the newsgroup here (just do a search for it). Tom Ogilvy wrote: Sub AABB() For Each Cell In Range("A1:A10") s = Cell.Value iloc = InStr(23, s, " ", vbTextCompare) s1 = Left(s, 22) s3 = Right(s, Len(s) - iloc) s2 = "***** " Cell.Value = s1 & s2 & s3 Next End Sub -- Regards, Tom Ogilvy "adiacc" wrote: Hi my 1st post so I hope I make sense: I have strings of data like the EG below 000000000000 0000 000 Andrea xxxx 222 3333 000000000000 0000 000 Louise xxxx 222 3333 So what I need to do is change the names to ***** the names can be different but the field is always the same number of characters and in the same location in the line The file is a TXT file. Any idea? Thank you Andrea |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
change a value on a string of data
Thank you that is great!!
one more question.....sorry.. so in the below example I need to do the replace after 23 caracters when the line starts with 1 and after 29 when the line starts with a 2 how would I do that? 100000000000 0000 000 Andrea xxxx 222 3333 200000000000 0000 000 xxxxx Louise 222 3333 Thank you!!!!!!!!!!!! "Tom Ogilvy" wrote: Sub AABB() For Each Cell In Range("A1:A10") s = Cell.Value iloc = InStr(23, s, " ", vbTextCompare) s1 = Left(s, 22) s3 = Right(s, Len(s) - iloc) s2 = "***** " Cell.Value = s1 & s2 & s3 Next End Sub -- Regards, Tom Ogilvy "adiacc" wrote: Hi my 1st post so I hope I make sense: I have strings of data like the EG below 000000000000 0000 000 Andrea xxxx 222 3333 000000000000 0000 000 Louise xxxx 222 3333 So what I need to do is change the names to ***** the names can be different but the field is always the same number of characters and in the same location in the line The file is a TXT file. Any idea? Thank you Andrea |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
change a value on a string of data
For Each Cell In Range("A1:A10")
s = Cell.Value if left(s,1) = "1" then i = 23 else i = 29 end if iloc = InStr(i, s, " ", vbTextCompare) s1 = Left(s, i-1) s3 = Right(s, Len(s) - iloc) s2 = "***** " Cell.Value = s1 & s2 & s3 Next -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub AABB() For Each Cell In Range("A1:A10") s = Cell.Value iloc = InStr(23, s, " ", vbTextCompare) s1 = Left(s, 22) s3 = Right(s, Len(s) - iloc) s2 = "***** " Cell.Value = s1 & s2 & s3 Next End Sub -- Regards, Tom Ogilvy "adiacc" wrote: Hi my 1st post so I hope I make sense: I have strings of data like the EG below 000000000000 0000 000 Andrea xxxx 222 3333 000000000000 0000 000 Louise xxxx 222 3333 So what I need to do is change the names to ***** the names can be different but the field is always the same number of characters and in the same location in the line The file is a TXT file. Any idea? Thank you Andrea |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
change a value on a string of data
Thank you!!
One problem when I convert back the file from Excel to .txt some of the lines (not all) Have a " as the 1st caracter in the line. That happens only in the .TXt file as if i look in the excel file I can not see the " Could you please help? "Tom Ogilvy" wrote: For Each Cell In Range("A1:A10") s = Cell.Value if left(s,1) = "1" then i = 23 else i = 29 end if iloc = InStr(i, s, " ", vbTextCompare) s1 = Left(s, i-1) s3 = Right(s, Len(s) - iloc) s2 = "***** " Cell.Value = s1 & s2 & s3 Next -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub AABB() For Each Cell In Range("A1:A10") s = Cell.Value iloc = InStr(23, s, " ", vbTextCompare) s1 = Left(s, 22) s3 = Right(s, Len(s) - iloc) s2 = "***** " Cell.Value = s1 & s2 & s3 Next End Sub -- Regards, Tom Ogilvy "adiacc" wrote: Hi my 1st post so I hope I make sense: I have strings of data like the EG below 000000000000 0000 000 Andrea xxxx 222 3333 000000000000 0000 000 Louise xxxx 222 3333 So what I need to do is change the names to ***** the names can be different but the field is always the same number of characters and in the same location in the line The file is a TXT file. Any idea? Thank you Andrea |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change color within string? | Excel Discussion (Misc queries) | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
bad data when i change string to double | Excel Programming | |||
Change value in a string | Excel Programming | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions |